SQL group and order

Question

I have multiple users with multiple entries recording times they arrive at destinations

Somehow, with my select query I would like to only show the most recent entries for each unique user name.

Here is the code that doesn't work:

SELECT * FROM $dbTable GROUP BY xNAME ORDER BY xDATETIME DESC

This does the name grouping fine, but as far as showing ONLY their most recent entry, is just shows the first entry it sees in the SQL table.

I guess my question is, is this possible?

Here is my data sample:

john  7:00
chris 7:30
greg 8:00
john 8:15
greg 8:30
chris 9:00

and my desired result should only be

john 8:15
chris 9:00
greg 8:30

Answer 1

How about something like

Select xName, MAX(xDATETIME) AS MaxDateVal
FROM $dbtable
GROUP BY xName
ORDER BY MaxDateVal

Answer 2

SELECT xNAME, MAX(xDATETIME)
FROM $dbTable 
GROUP BY xNAME 
ORDER BY xDATETIME DESC

Answer 3

SELECT MAX(xNAME), MAX(xDATETIME) FROM $dbTable GROUP BY xNAME ORDER BY xDATETIME DESC

Answer 4

The problem with your query is that when you use GROUP BY, you have to specify the aggregate function for the fields that are not in the GROUP BY clause.

You may want to try the following instead:

SELECT     u.*
FROM       users u
INNER JOIN (
           SELECT   xName, MAX(xDatetime) max_time 
           FROM     users 
           GROUP BY xName
           ) sub_u ON (sub_u.xName = u.xName AND 
                       u.xDateTime = sub_u.max_time);

The above query can be tested as follows:

CREATE TABLE users (id int, xName varchar(100), xDateTime datetime);

INSERT INTO users VALUES (1, 'a', '2010-03-11 00:00:00');
INSERT INTO users VALUES (2, 'a', '2010-03-11 01:00:00');
INSERT INTO users VALUES (3, 'a', '2010-03-11 02:00:00');
INSERT INTO users VALUES (4, 'b', '2010-03-11 01:00:00');
INSERT INTO users VALUES (5, 'b', '2010-03-11 02:00:00');
INSERT INTO users VALUES (6, 'b', '2010-03-11 03:00:00');
INSERT INTO users VALUES (7, 'c', '2010-03-11 06:00:00');
INSERT INTO users VALUES (8, 'c', '2010-03-11 05:00:00');

-- Query Result:

+----+-------+---------------------+
| id | xName | xDateTime           |
+----+-------+---------------------+
|  3 | a     | 2010-03-11 02:00:00 |
|  6 | b     | 2010-03-11 03:00:00 |
|  7 | c     | 2010-03-11 06:00:00 | 
+----+-------+---------------------+

If you want to order the result-set by the max_time field, simply add ORDER BY u.xDateTime DESC at the end of the query.

Answer 5

Also you can do this:

Select 
  xName, xDATETIME 
from 
  $dbTable t1
where 
  xDATETIME = 
    (select max(xDATETIME) from $dbTable t2
     where t1.xName=t2.xName)
order by xDATETIME DESC