CODEWITHSUNDEEP
sqlgrouping
John Lambert
John Lambert

Reputation: 31

SQL group and order

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

Upvotes: 3

Views: 982

Answers (5)

Daniel Vassallo
Daniel Vassallo

Reputation: 344311

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.

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166396

How about something like

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

Upvotes: 8

Eugene
Eugene

Reputation: 2985

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

Upvotes: 0

Chris Lercher
Chris Lercher

Reputation: 37778

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

Upvotes: 0

Dustin Laine
Dustin Laine

Reputation: 38503

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

Upvotes: 3

Related Questions