I have a complex query, from a subquery. I'm trying to return a unique column. Here is a simplified example: (Note: EMPID Should be unique, taking the latest FINISHDATETIME, if there is a dup)
CREATE TABLE STATION (ID INTEGER PRIMARY KEY
, EMPID INT
, SCORE INT
, FINISHDATETIME DATETIME
)
INSERT INTO STATION VALUES (199, 163, 92, '4/28/2008 16:54')
INSERT INTO STATION VALUES (200, 81, 96, '4/30/2008 13:03')
INSERT INTO STATION VALUES (201, 85, 96, '4/30/2008 13:21')
INSERT INTO STATION VALUES (202, 181, 100, '5/1/2008 14:28')
INSERT INTO STATION VALUES (203, 90, 89, '5/7/2008 12:17')
INSERT INTO STATION VALUES (204, 79, 80, '3/15/2008 0:00')
INSERT INTO STATION VALUES (205, 81, 20, '4/30/2008 13:02')
INSERT INTO STATION VALUES (206, 79, 67, '5/25/2008 1:10')
INSERT INTO STATION VALUES (207, 79, 33, '3/15/2008 0:00')
INSERT INTO STATION VALUES (208, 254, 100, '6/5/2008 1:15')
SELECT ID, EMPID, MAX(SCORE) AS SC, FINISHDATETIME
FROM STATION AS tt
WHERE (tt.FINISHDATETIME =
(SELECT MAX(i.FINISHDATETIME) FROM STATION AS i WHERE i.EMPID = tt.EMPID))
AND
(tt.ID =
(SELECT MAX(i.ID) FROM STATION AS i WHERE i.EMPID = tt.EMPID))
GROUP BY tt.ID, tt.EMPID, tt.FINISHDATETIME
ORDER BY tt.EMPID
DROP TABLE STATION
Here is the result set I get:
201 85 96 4/30/08 1:21 PM
203 90 89 5/7/08 12:17 PM
199 163 92 4/28/08 4:54 PM
202 181 100 5/1/08 2:28 PM
208 254 100 6/5/08 1:15 AM
It should be:
206 79 67 5/25/08 1:10 AM
200 81 96 4/30/08 1:03 PM
201 85 96 4/30/08 1:21 PM
203 90 89 5/7/08 12:17 PM
199 163 92 4/28/08 4:54 PM
202 181 100 5/1/08 2:28 PM
208 254 100 6/5/08 1:15 AM
Where am I going wrong?
Start Free Trial
