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

[ClimoC]

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

 

[ClimoC]

OK, looking back, I see I missed this statement:

So, you may have multiple records for each titleid/txdate combination. So we need to make a few alterations.

First, do you have some other unique field in this table? If so, what is it?
If not, please add an Autonumber field to the table.

Now, let's confirm what you want to return.
- Do you want only one record for each titleid, or one record for each titleid/txdate combination (so you could have multiple titleid records, but all with different dates)?
- When you run into these "duplicate" situations, does it matter which record you return and which record is dropped?
If yes, what is the logic of which one you want returned?

do you have some other unique field in this table? If so, what is it? Yes, standard access Autonumber field, 'ID'

- Do you want only one record for each titleid, or one record for each titleid/txdate combination (so you could have multiple titleid records, but all with different dates)? One record for each titleid

- When you run into these "duplicate" situations, does it matter which record you return and which record is dropped? It does not matter which one, the meta data is what is being counted - sum of duration and an overall headcount

Thanks Joe, really appreciate your help with this. Myself and an R programmer are the only two SQL literate(ish) people in the building...

 

[Joe4]

OK, that helps explain why you were trying to do it in the fashion you were. It looks like we will need another level of nesting.

It looks a little messy, but this appears to work:

SELECT 
    rst.txdate as TX_Date, 
    rst.titleid as Title_ID, 
    rst.status, 
    rst.duration, 
    rst.allocated, 
    rst.assigned, 
    rst.title 
FROM 
    [work] rst
INNER JOIN
[COLOR=#ff0000](SELECT 
    pp.titleid,
    pp.txdate,
    min(pp.ID) as MinID
FROM
    [work] pp
INNER JOIN[/COLOR]
[COLOR=#0000ff](SELECT
    titleid, 
    min(txdate) as MinDate
FROM 
    [work] 
WHERE 
    archive = 0 
GROUP BY 
    titleid) run1[/COLOR]
[COLOR=#ff0000]ON
    pp.titleid=run1.titleid
    AND pp.txdate=run1.MinDate
WHERE 
    pp.archive = 0    
GROUP BY
    pp.titleid,
    pp.txdate) run2[/COLOR]
ON
    rst.ID=run2.MinID

I color-coded it to try to make it easier to see the different levels of nesting.

 

[ClimoC]

OK, that helps explain why you were trying to do it in the fashion you were. It looks like we will need another level of nesting.

It looks a little messy, but this appears to work:

SELECT 
    rst.txdate as TX_Date, 
    rst.titleid as Title_ID, 
    rst.status, 
    rst.duration, 
    rst.allocated, 
    rst.assigned, 
    rst.title 
FROM 
    [work] rst
INNER JOIN
[COLOR=#ff0000](SELECT 
    pp.titleid,
    pp.txdate,
    min(pp.ID) as MinID
FROM
    [work] pp
INNER JOIN[/COLOR]
[COLOR=#0000ff](SELECT
    titleid, 
    min(txdate) as MinDate
FROM 
    [work] 
WHERE 
    archive = 0 
GROUP BY 
    titleid) run1[/COLOR]
[COLOR=#ff0000]ON
    pp.titleid=run1.titleid
    AND pp.txdate=run1.MinDate
WHERE 
    pp.archive = 0    
GROUP BY
    pp.titleid,
    pp.txdate) run2[/COLOR]
ON
    rst.ID=run2.MinID

I color-coded it to try to make it easier to see the different levels of nesting.

Aaaah fantastic. Thank you very much Joe for your help. We knew it wasn't as straight forward as it first seemed! This works perfectly!

 

[Joe4]

Glad we got it to finally work out!