Max value for each of many records

[Soundsmith]

I have a table which holds transaction records-many records per claim, many claims. primary key is combination of cnum plus seq, both Longs.

I would like to create an SQL query to display all the fields in the table, but only the most recent transaction (highest seq for each cnum).

It seems to me this should be fairly simple, but I don't see any references in the archives, and nothing I can do works. Group By has many non-aggregate fields, and top 1 just produces a single record. I can do this with a dual query, that pick cnum and MAX(seq) group by cnum and use this to select * where both criteria are met, but I would like to use a single query if possible.

Any ideas would be appreciated. Thanks
David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com

 

[cmmrfrds]

This type of query will work if you only have one record with the max date for each key, otherwise, you get all the records that match the max date.

SELECT Y.stor_id, Y.ord_date, Y.qty
FROM sales Y
WHERE Y.ord_date =
(SELECT max(ord_date)
FROM sales X
where X.stor_id = Y.stor_id)
order by Y.ord_date desc

 

[BoxHead]

David,

I put together a small table {tblSoundsmith} with fields 'cnum', 'seq' and a test field called 'AnotherThing'.

Data looked like:

cnum seq AnotherThing
--------------------------------
12345 1 should be 4
12345 2 should be 4
12345 3 should be 4
12345 4 should be 4
23456 1 should be 3
23456 2 should be 3
23456 3 should be 3
34567 1 should be 18
34567 2 should be 18
34567 3 should be 18
34567 4 should be 18
34567 5 should be 18
34567 7 should be 18
34567 10 should be 18
34567 18 should be 18
45678 1 should be 4
45678 2 should be 4
45678 3 should be 4
45678 4 should be 4

The following query ...

SELECT tblSoundsmith.cnum, Max(tblSoundsmith.seq) AS MaxOfseq
FROM tblSoundsmith
GROUP BY tblSoundsmith.cnum;

... produced the following results:

cnum MaxOfseq AnotherThing
--------------------------------------
12345 4 should be 4
23456 3 should be 3
34567 18 should be 18
45678 4 should be 4

Is that what you were after?


HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[Soundsmith]

John: Thanks, but I can't use GROUP BY because I need all fields returned, not just the aggregates.

Cmmrfrds: I tried your idea (I forgot about self-joining-thanks!) but I seem to be returning a Cartesian product-I've had to interrupt it after 5 minutes of waiting...

Here's the actual code. I need to return the maximum seq (most recent record) value for each cnum (claim number) of 6000 or so. Do you see where I'm going wrong?

SELECT Y.*
FROM qryPayLink Y
WHERE Y.seq =
    (SELECT max(seq)
    FROM qryPayLink X
    where  X.cnum = Y.cnum)
order by Y.cnum

Thanks.
David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com

 

[BoxHead]

David,

Why would 'group by' prevent you from returning the remaining fields in your table? I didn't include any extra fields in my example because it was an example - not a real table.

What am I missing?


John John

Use what you have,
Learn what you can,
Create what you need.

 

[cmmrfrds]

The only thing I can see different is that qryPayLink is a query instead of a table. My example was run against an Sql Server table, so, maybe Jet does something different because you are using a query. Can you run it directly against your table?

 

[Soundsmith]

John, according to the Access help file,

"All fields in the SELECT field list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function."

Basically, I can't group on items that have no reference in the base query.

Cmmrfrds: I tried this with a table, and got an answer in 2 seconds. Substituting the query (three joined tables, but no difficulties in other operations) it froze. Even with just the two involced fields, it never came back in 5 minutes.

This gives me a workaround. I'll construct the table with just the active records (I've got to do this anyway) and create the list from that table.

Thanks.
David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com

 

[BoxHead]

David,

'First' is an SQL aggregate function. It elimnates the duplicates and only returns the Max for each.

HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[BoxHead]

Sorry, David. Since you're looking up max, use 'Last' as the function. John

Use what you have,
Learn what you can,
Create what you need.

 

[Soundsmith]

John,
I'm not familiar with First and Last as SQL statements. I'll give that a try.

Thanks
David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com