CODEWITHSUNDEEP
sqlms-access
Rick
Rick

Reputation: 2308

SQL Max Function per group

I have a complex query and which may return more than one record per group. There is a field that has a numeric sequential number. If in a group there is more than one record returned I just want the record with the highest sequential number.

I’ve tried using the SQL MAX function, but if I try to add more than one field it returns all records, instead of the one with the highest sequential field in that group.

I am trying to accomplish this in MS Access.

Edit: 4/5/11 Trying to create a table as an example of what I am trying to do

I have the following table:

tblItemTrans

ItemID(PK)
Eventseq(PK)
ItemTypeID
UserID

Eventseq is a number field that increments for each ItemID. (Don’t ask me why, that’s how the table was created.) Each ItemID can have one or many Evenseq’s. I only need the last record (max(Eventseq)) PER each ItemTypeID.

Hope this helps any.

Upvotes: 0

Views: 1842

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

If your SequentialNumber is an ID (unique across the table), then you could use

select *
from tbl
where seqnum in (
    select max(seqnum) from tbl
    group by groupcolumn)

If it is not, an alternative to Lamak's query is the Access domain function DMAX

select *
from tbl
where seqnum = DMAX("seqnum", "tbl", "groupcolumn='" & groupcolumn & "'")

Note: if the groupcolumn is a date, use # instead of single quotes ' in the above, if it is a numeric, remove the single quotes.

Upvotes: 2

Lamak
Lamak

Reputation: 70678

SELECT A.*
FROM YourTable A
INNER JOIN (SELECT GroupColumn, MAX(SequentialColumn) MaxSeq
            FROM YourTable
            GROUP BY GroupColumn) B
ON A.GroupColumn = B.GroupColumn AND A.SequentialColumn = B.MaxSeq

Upvotes: 4

Related Questions