SQL group and order

Question

I have several users with multiple record entries that they arrive at destinations

Be that as it may, with my query of choice, I would like to show only the most recent entries for each unique username.

Here is the code that does not work:

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

It is perfectly grouped by name, but ONLY their most recent record is shown, it simply shows the first record that it sees in the SQL table.

I think my question is, is this possible?

Here is my sample data:

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

and my desired result should 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

, GROUP BY , GROUP BY.

:

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);

:

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 in the field max_time, just add ORDER BY u.xDateTime DESCto the end of the query.

Answer 4

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

Answer 5

You can also 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