Group By and Order By query issue

[lintz]

I've got a log table below which has the below data. What I want to get from my query is the latest log entry for each user.

ID UserID LogTimeDate
1 1234 12/12/2010 09:15:00 AM
2 1234 12/12/2010 03:15:00 PM
3 5678 11/12/2010 09:15:00 AM
4 5678 15/12/2010 09:15:00 AM
5 789 09/12/2010 09:15:00 AM
6 789 08/12/2010 09:15:00 AM

The current query I have is:

select * FROM logtable GROUP BY UserID ORDER BY LogTimeDate DESC;

but the results come out as:
ID UserID LogTimeDate
1 1234 12/12/2010 09:15:00 AM
3 5678 11/12/2010 09:15:00 AM
6 789 08/12/2010 09:15:00 AM

where as the results should be:
ID UserID LogTimeDate
2 1234 12/12/2010 03:15:00 PM
4 5678 15/12/2010 09:15:00 AM
5 789 09/12/2010 09:15:00 AM

It seems to be the Order By is ignored if a Group By is used. Anyone able to help with a query which will return my desired results?

[si_the_geek]

The ordering looks fine to me, but note that it takes place after everything else (including the Group By).

I must say that I am surprised your query runs at all, because it doesn't make sense in terms of the Group By, so you should be getting an error.


To get the data you want (apart from the ID) you can use a simple Group By:

SELECT UserID, Min(LogTimeDate) 
FROM logtable 
GROUP BY UserID

If you do actually want the ID too it will be a bit more complex.

[lintz]

Thanks for the quick reply si_the_geek but the results don't quite come out as expected. Your query now returns the last log for each user but when displayed the latest log is not at the top.

Below is a modified table.
ID UserID LogTimeDate
1 1234 12/12/2010 09:15:00 AM
2 1234 15/12/2010 09:15:00 AM
3 5678 11/12/2010 09:15:00 AM
4 5678 15/12/2010 03:15:00 PM
5 789 09/12/2010 09:15:00 AM
6 789 08/12/2010 09:15:00 AM

If I then run your query the results are:
ID UserID LogTimeDate
2 1234 15/12/2010 09:15:00 AM
4 5678 15/12/2010 03:15:00 PM
5 789 09/12/2010 09:15:00 AM

But as UserID 5678 has a later log than UserID 1234, it should be the first one returned. I even added a ORDER BY to the end of your query but still can't get "4 5678 15/12/2010 03:15:00 PM" as the first record returned.

[techgnome]

I think there's something you aren't telling us. And looking at those dates, I have a suspicion as to what that missing piece of info is. Like for instance... is the LogDateTime a STRING field by chance? The reason I ask is because 15/12/2010 is an invalid format in most DBs I've used... most store dates as mm/dd/yyyy NOT dd/mm/yyyy ... so I'm thinking that you've used string (or varchar) as the datatype to force it to store the date the way you want to. It also explains the sorting... as 09 is greater than 03... that's why 15/12/2010 09:15 sorts before 15/12/2010 03:00 ....

-tg

[sapator]

There may be also a chance that min are not parsing AM and PM.I can't be sure because the problem occurred to me in Greek collation db's.

[si_the_geek]

In addition to techgnome's point, I don't understand how you can be getting the ID column. Even if you added it to the field list in my query, it would not return the kind of results you say you are getting.

[abhijit]

Thanks for the quick reply si_the_geek but the results don't quite come out as expected. Your query now returns the last log for each user but when displayed the latest log is not at the top.

Below is a modified table.
ID UserID LogTimeDate
1 1234 12/12/2010 09:15:00 AM
2 1234 15/12/2010 09:15:00 AM
3 5678 11/12/2010 09:15:00 AM
4 5678 15/12/2010 03:15:00 PM
5 789 09/12/2010 09:15:00 AM
6 789 08/12/2010 09:15:00 AM

If I then run your query the results are:
ID UserID LogTimeDate
2 1234 15/12/2010 09:15:00 AM
4 5678 15/12/2010 03:15:00 PM
5 789 09/12/2010 09:15:00 AM

But as UserID 5678 has a later log than UserID 1234, it should be the first one returned. I even added a ORDER BY to the end of your query but still can't get "4 5678 15/12/2010 03:15:00 PM" as the first record returned.

What did you ORDER BY? Did you ORDER BY min(LogTimeDate) ?

In addition to techgnome's point, I don't understand how you can be getting the ID column. Even if you added it to the field list in my query, it would not return the kind of results you say you are getting.

I wonder if the ID column is a row # as displayed by several sql clients like TOAD, PL/SQL Developer etc.