tags:

views:

92

answers:

5

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
+8  A: 

How about something like

Select xName, MAX(xDATETIME) AS MaxDateVal
FROM $dbtable
GROUP BY xName
ORDER BY MaxDateVal
astander
i tried to MAX xDATEIME but it gives me the most recent entry for the table, NOT the most recent for each unique xNAME
John Lambert
Did you try the Query as provided? Also using the **GROUP BY**?
astander
Yes, if I put in that query all I get is names, and the names aren't sorted by entry time.
John Lambert
Can you try again please? I edited the answer.
astander
did you mean to put AS between the MAX(xDATEIME) and MaxDateVal?
John Lambert
You can put AS, but in SQL SERVER it is implied.
astander
ok didn't know, I tried it and it looks different. Give me a minute to interpret the data to see if it's correct. I'll post the result.
John Lambert
+3  A: 
SELECT xNAME, MAX(xDATETIME)
FROM $dbTable 
GROUP BY xNAME 
ORDER BY xDATETIME DESC
Dustin Laine
I dont think the ORDER BY is required here, just an extra step for the engine X-)
astander
+1 Agree, just copied his query. But if he wanted it list in order then it would work.
Dustin Laine
Provided results does not seems ordered what so all... Check again
astander
still working on it, at first, I was outputting NOTHING, so I'm trying to figure out what's happening
John Lambert
A: 
SELECT MAX(xNAME), MAX(xDATETIME) FROM $dbTable GROUP BY xNAME ORDER BY xDATETIME DESC
Chris Lercher
`MAX(xNAME)`? I don't think you can call MAX on a column you specified in a GROUP BY.
R. Bemrose
**MAX(xName)?** are you sure. This will only return a single row...
astander
It should return a single row per group, like with MAX(xDATETIME). It may be unnecessary though.
Chris Lercher
... but it works both ways, just tried it (with Postgres)
Chris Lercher
+1  A: 

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.

Daniel Vassallo
Although I didn't try all of the responses below, this one worked transparently. Thank you!
John Lambert
A: 

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
Eugene