Nasevz Posted November 29, 2005 Share Posted November 29, 2005 My table has this columns:rowId, computerName, logonTime, logoffTime, userNameI would like to select a row for each computerName where logonTime has highest value (To see rows where last logon happened for each computer).I am trying something like SELECT computerName, max(logonTime), logoffTime, userName from log GROUP BY computerName HAVING logonTime=max(logonTime) ???but I need to get row where max(logonTime) appears in each group (computerName).Can somebody help me? Link to comment Share on other sites More sharing options...
Skemcin Posted November 29, 2005 Share Posted November 29, 2005 something like this shuld work: SELECT distinct computerName, logonTimeFROM tablenameORDER BY logonTime DESC if I am reading your request correct, this should suffice Link to comment Share on other sites More sharing options...
Nasevz Posted November 29, 2005 Author Share Posted November 29, 2005 something like this shuld work:SELECT distinct computerName, logonTimeFROM tablenameORDER BY logonTime DESC <{POST_SNAPBACK}> This would select all logontimes for all computers because all logontimes would most likely be different. I would like only the logontime that has the highest value for each computer.The computer names in the table are repeated many times (each time somebody logs on on that computer and there are many computers, not one) and I would like only the last logons for all computers. Link to comment Share on other sites More sharing options...
aspnetguy Posted November 29, 2005 Share Posted November 29, 2005 SELECT distinct computerName, MAX(logonTime)FROM tablenameORDER BY logonTime DESCdoes this work?If not, I'll setup some test data and give it a proper try. Link to comment Share on other sites More sharing options...
Skemcin Posted November 29, 2005 Share Posted November 29, 2005 This would select all logontimes for all computers because all logontimes would most likely be different. I would like only the logontime that has the highest value for each computer.The computer names in the table are repeated many times (each time somebody logs on on that computer and there are many computers, not one) and I would like only the last logons for all computers. The query I submited should return the results you want. Lets break it down:SELECT distinct computerName, MAX(logonTime)FROM tablenameORDER BY logonTime DESC 1.) the "distinct" notation in the "select" statement will make sure that the database only return one "computerName". So if you have 20 computerNames and they appear each anywhere from 50 to 2000 or more times in the database, the "distinct" will only grab the 20 computerNames since those are, well, distinct.2.) by have the query sort by the logonTime in descending order, it wil take the largest value at begin build the record set from that value.On a side note, your data schematic seems to imply that logonTime is the time in which a user logs in to the site and the logoffTime is the time in which they logged off. So to measure the longest time a particular computerName if logged into, you would have to subtract the two to get the duration of time - if thats what your after. If you are, I have a couple other questions - but I guess I need to make sure I am on the same page with you.Does my SQl work?Since we are only getting the distinct computerNames and we are building our record set from the largest value, your end result will be 20 records (using my example) each of which has the hightest logonTime. Link to comment Share on other sites More sharing options...
aspnetguy Posted November 30, 2005 Share Posted November 30, 2005 in your original post you did not have the MAX(). I think that is why the results weren't what he wanted. Link to comment Share on other sites More sharing options...
Nasevz Posted November 30, 2005 Author Share Posted November 30, 2005 The query I submited should return the results you want. Lets break it down:SELECT distinct computerName, MAX(logonTime)FROM tablenameORDER BY logonTime DESC This is the error message from the select statementMsg 8120, Level 16, State 1, Line 1Column 'log.computername' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.Msg 145, Level 15, State 1, Line 1ORDER BY items must appear in the select list if SELECT DISTINCT is specified.1. distinct is applyed to whole row, not on computername only, so 'distinct computername, logontime' would return all distinct combinations of them.2. MAX(logontime) returns the maximum value of logontime for whole table and it is only one.If distinct computername returns 20 computernames and MAX(logontime) only one, the right rows can't be returnedselect computername, max(logontime) from log group by computernamewould return the right values, but I need a whole row (including userid and logofftime), and if I add userID and logofftime to select statemet, they also must be either in agregate function or in a group by clause. I dont know if it is an agregate function that returns the value retreived by finding the max value of another column (return logofftime and userID from the row where logontime has maximum value) Link to comment Share on other sites More sharing options...
Kcarson Posted November 30, 2005 Share Posted November 30, 2005 Alright, to get what you are wanting try this: SELECT distinct computerName, MAX(logonTime)FROM tablename GROUP BY computerName This will first group all the computerNames and keep just one of each (distinct), then from that group it will find the max logon time. This should take care of the errors you received and give you back the data you are wanting. Link to comment Share on other sites More sharing options...
Nasevz Posted November 30, 2005 Author Share Posted November 30, 2005 SELECT distinct computerName, MAX(logonTime)FROM tablename GROUP BY computerName This will first group all the computerNames and keep just one of each (distinct), then from that group it will find the max logon time. <{POST_SNAPBACK}> From previous message: select computername, max(logontime) from log group by computernamewould return the right values, but I need a whole row (including userid and logofftime), and if I add userID and logofftime to select statemet, they also must be either in agregate function or in a group by clause. I dont know if it is an agregate function that returns the value retreived by finding the max value of another column (return logofftime and userID from the row where logontime has maximum value) BTW grouping by computername implys distinct computernames, so with or without distinct, the result is the same. Link to comment Share on other sites More sharing options...
Kcarson Posted November 30, 2005 Share Posted November 30, 2005 From previous message: select computername, max(logontime) from log group by computernamewould return the right values, but I need a whole row (including userid and logofftime), and if I add userID and logofftime to select statemet, they also must be either in agregate function or in a group by clause. I dont know if it is an agregate function that returns the value retreived by finding the max value of another column (return logofftime and userID from the row where logontime has maximum value) BTW grouping by computername implys distinct computernames, so with or without distinct, the result is the same.<{POST_SNAPBACK}> Sorry, I must have been sleeping during the first few posts when you mentioned the other values you wanted returned...sorry :)Well, how will you identify the userid and logofftime that you want? Do you want the ones on the same row as the max(logontime)? If so I imagine you could select the max(logofftime) and that should get you at least one of the values you wanted (since the max logoff time will coincide with the max logon time (just a guess though))...as for the UserID, if you want the max(logontime) and max(logofftime) for each userID on each ComputerName, then you would do, if you just want the one user for that max(logontime) then I am unsure as to how that would be accomplished:SELECT computerName, userName, max(Logontime), max(logofftime)FROM log GROUP By computerName, userName As for the group by statement I made...I stand corrected, I sometimes type before I think :)Anyways, let me know if the above works for what you were looking for. Link to comment Share on other sites More sharing options...
Nasevz Posted December 1, 2005 Author Share Posted December 1, 2005 The problem is that I would like whole row with all columns for each computename where max(logontime) has highest value and max(logofftime) is not necessarily in the same row with max(logontime) because sometimes workstations fail to write logofftime (reset, power failure ...). Maybe it can be written with subqueries, but I am not very experianced in SQL.Thank you for trying to help me. Link to comment Share on other sites More sharing options...
Kcarson Posted December 1, 2005 Share Posted December 1, 2005 Maybe it can be written with subqueries, but I am not very experianced in SQL.Thank you for trying to help me.<{POST_SNAPBACK}> You know I hadn't even thought of using subqueries, but that would work, I just tried it myself, and it seemed to do the trick, but try it yourself to make sure....here you go:SELECT distinct A.computerName, A.userName, A.Logontime, A.logofftimeFROM log AWHERE A.logontime = (SELECT max(b.logontime) FROM log B WHERE B.computerName = A.computerName) All this should do is pull back the data from the row where the max(logontime) exists for each computerName. I include the B.computerName = A.computerName so that it will return the max for each computerName rather than just the max for the entire table.Let me know if you have any questions. Link to comment Share on other sites More sharing options...
Nasevz Posted December 2, 2005 Author Share Posted December 2, 2005 You know I hadn't even thought of using subqueries, but that would work, I just tried it myself, and it seemed to do the trick, but try it yourself to make sure....here you go:SELECT distinct A.computerName, A.userName, A.Logontime, A.logofftimeFROM log AWHERE A.logontime = (SELECT max(b.logontime) FROM log B WHERE B.computerName = A.computerName) All this should do is pull back the data from the row where the max(logontime) exists for each computerName. I include the B.computerName = A.computerName so that it will return the max for each computerName rather than just the max for the entire table.Let me know if you have any questions. <{POST_SNAPBACK}> If I understand your query correctly, you are suggesting me to have two identical tables with different names? Link to comment Share on other sites More sharing options...
Nasevz Posted December 2, 2005 Author Share Posted December 2, 2005 Your query solved the problem, thank you. Link to comment Share on other sites More sharing options...
Kcarson Posted December 2, 2005 Share Posted December 2, 2005 Your query solved the problem, thank you.<{POST_SNAPBACK}> Not a problem, I am glad I was able to help. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now