Jump to content

Select a row from grouped data


Nasevz

Recommended Posts

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

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

something like this shuld work:
SELECT distinct computerName, logonTimeFROM tablenameORDER BY logonTime DESC

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

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

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

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

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.

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

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.

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

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

Maybe it can be written with subqueries, but I am not very experianced in SQL.Thank you for trying to help me.

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

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.

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...