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]

Did you try doing it as a set of two separate queries, like I mentioned in my post?

As far as I know you can't run multiple queries in a linked pivot table

 

[Joe4]

As far as I know you can't run multiple queries in a linked pivot table

You shouldn't need to. You only call/open the last one.

Let's just try it to see if you get the results you want before we worry about the pivot table, OK?
If that works, it shouldn't be a problem them to nest them anyhow. We just want to make sure it returns the results you are looking for before proceeding (and so you learn/get a feeling for how this all works).

 

[ClimoC]

You shouldn't need to. You only call/open the last one.

Let's just try it to see if you get the results you want before we worry about the pivot table, OK?
If that works, it shouldn't be a problem them to nest them anyhow. We just want to make sure it returns the results you are looking for before proceeding (and so you learn/get a feeling for how this all works).

Okay, so this appears to work:

SELECT min(ti.txdate), ti.titleid FROM (SELECT DISTINCT pp.titleid, pp.txdate FROM [work] pp WHERE pp.archive = 0 GROUP BY pp.titleid, pp.txdate) ti GROUP BY ti.titleid

But now how and where to put the join is beyond me. If I put the likes of (assume I alias my table again as 'wa') "wa.status, wa.allocated, wa.assigned" in the main query, I get the 'column not part of an aggregate function' error. If I put it inside the subquery, it will then use those columns as part of what defines the 'DISTINCT' part, and return erroneous results

 

[ClimoC]

...I would have thought something like this, but this gives me an unhelpful error of 'No value given for one or more required parameters'

SELECT agg.txdate as TX_Date, agg.titleid as Title_ID, rst.status, rst.duration, rst.allocated, rst.assigned, rst.title FROM (SELECT min(ti.txdate), ti.titleid FROM (SELECT DISTINCT pp.titleid, pp.txdate FROM [work] pp WHERE pp.archive = 0 GROUP BY pp.titleid, pp.txdate) ti GROUP BY ti.titleid) agg LEFT JOIN [work] rst ON rst.titleid=agg.titleid

 

[Joe4]

Okay, so this appears to work:

[COLOR=#333333]SELECT min(ti.txdate), ti.titleid FROM (SELECT DISTINCT pp.titleid, pp.txdate FROM [work] pp WHERE pp.archive = 0 GROUP BY pp.titleid, pp.txdate) ti GROUP BY ti.titleid[/COLOR]

I think you are doing some unnecessary work there.

Can you confirm that the following code returns the same records that the code above does?

SELECT
    pp.titleid, 
    min(pp.txdate) 
FROM 
    [work] pp 
WHERE 
    pp.archive = 0 
GROUP BY 
    pp.titleid

 

[Joe4]

And see if this does everything you need, getting you all the way to your end goal:

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=#0000ff]([/COLOR][COLOR=#0000ff]SELECT[/COLOR]
[COLOR=#0000ff]    titleid, [/COLOR]
[COLOR=#0000ff]    min(txdate) as MinDate[/COLOR]
[COLOR=#0000ff]FROM [/COLOR]
[COLOR=#0000ff]    [work] [/COLOR]
[COLOR=#0000ff]WHERE [/COLOR]
[COLOR=#0000ff]    archive = 0 [/COLOR]
[COLOR=#0000ff]GROUP BY [/COLOR]
[COLOR=#0000ff]    titleid) agg[/COLOR]
ON 
    rst.titleid=agg.titleid
    AND rst.txdate=agg.MinDate

I highlighted in blue the first part of the query, to show how I nested one inside the other to do it all in one query.

 

[ClimoC]

[COLOR=#333333]SELECT min(ti.txdate), ti.titleid FROM (SELECT DISTINCT pp.titleid, pp.txdate FROM [work] pp WHERE pp.archive = 0 GROUP BY pp.titleid, pp.txdate) ti GROUP BY ti.titleid[/COLOR]

I think you are doing some unnecessary work there.

Can you confirm that the following code returns the same records that the code above does?

SELECT
    pp.titleid, 
    min(pp.txdate) 
FROM 
    [work] pp 
WHERE 
    pp.archive = 0 
GROUP BY 
    pp.titleid

hey Joe

Yeah it does seem to...

So odd... I swore that was the first query I tried, and I always got back something about aggregate functions and the Group by bit

 

[Joe4]

In the query you posted, you were including "txDate" in your Group By clause, which you do not want to do, as you are looking for the minimum value. It looked as if you thought you needed to get DISTINCT values first, then apply the Aggregate, which is not the cause. The Aggregate Query itself will not return duplicate entries, so there is no need to do that.

So, does the code in post #16 above, where I nest that query back into a query with the original table return everything you need then?
(not sure if you saw that post too).

 

[ClimoC]

And see if this does everything you need, getting you all the way to your end goal:

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=#0000ff]([/COLOR][COLOR=#0000ff]SELECT[/COLOR]
[COLOR=#0000ff]    titleid, [/COLOR]
[COLOR=#0000ff]    min(txdate) as MinDate[/COLOR]
[COLOR=#0000ff]FROM [/COLOR]
[COLOR=#0000ff]    [work] [/COLOR]
[COLOR=#0000ff]WHERE [/COLOR]
[COLOR=#0000ff]    archive = 0 [/COLOR]
[COLOR=#0000ff]GROUP BY [/COLOR]
[COLOR=#0000ff]    titleid) agg[/COLOR]
ON 
    rst.titleid=agg.titleid
    AND rst.txdate=agg.MinDate

I highlighted in blue the first part of the query, to show how I nested one inside the other to do it all in one query.

Ah... no sorry unfortunately this doesn't work - returns multiples from the same day. At least it's not returning ones from other days...

it's also returning things that don't meet the criteria of archive = 0...

 

[Joe4]

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

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)

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?