Excel: Brain explosion - Access SQL Select with: MIN date value, grouped by another field, BUT return ALL rows

Hey hey hey This has had me flustered for nearly the whole day and I'm at my wits' end. A table has multiple 'titleids' (just a number column), with some different dates and some the same (so you could see the same titleid for twice on the 1st Jan, once on the 2nd Jan, once on the 5th Jan) I need to get the single earliest (by date) occurrence of each titleid - though I need to return the other 20 odd columns in the table as well. This works (I think... looks okay) for returning the earliest occurrence of each titleid, uniquely. But I cannot for the life of me get the JOIN to work (so far as in that I've given up even trying)

SELECT ti.mydate as 'My_Date', ti.titleid AS 'TITLE_ID' FROM (SELECT DISTINCT rp.titleid, rp.mydate FROM [work] rp ORDER BY rp.mydate ASC) ti
But I can't get the JOIN going!!! If I can't use a 'SELECT *' in the join, for the sake of an example/pointing in the right direction, a couple of the field anmes will be 'status', 'assigned', 'archive' Can anyone help? Despite being a VBA guru (relatively) around my place, and being handy with ORACLE SQL, I can't get access to behave Thanks C

This question generated 24 answers. To proceed to the answers, click here.

This thread is current as of May 21, 2014.

For more resources for Microsoft Excel