I am trying to get all the last records of a clients document in a table. The table consists of a docnumber, code (who changed it), a changedate, and a clientID.
rec1 123 AAA 01-01-2003 123123
rec2 123 EEE 01-02-2004 123123
rec3 222 CCC 31-12-2003 123123
rec4 222 DDD 31-01-2004 123123
rec5 222 FFF 01-02-2004 123123
When I do a select, I want to get rec2 and rec5 (rec2 is the last record of document 123 of client 123123 and rec5 is the last record of document 222 of client 123123.
One minor point is that it is a correlated subquery so it will run OK providing you havent got lots client ids. If you do then do the following
SELECT m.docnumber, m.code, m.changedate, m.clientid
FROM mytable m,
(SELECT docnumber, max(m1.changedate) lastchange FROM mytable m1 group by docnumber) x
WHERE m.changedate = x.lastchange
Of course this is assuming changedate is unique (date fields have a resolution of one second). If you have a large number of rows then an index on docnumber, changedate would also help.