selecting records with largest sequence number

[jakemelon]

Hey,

I have a table with 400,000 records. It has a bunch of fields of which two are of interest to me.

One is called User_ID and the other is sequence_num

In this table I could have one unique ID displayed 200 times but each occurence of it will have a unique sequence number.

for example:

User_ID sequence_num
22222222 150001
22222222 150002
22222222 150003
22222222 150004
33333333 174100
33333333 174101
66666666 220006
66666666 220007

and so on.

I need to be able to automatically extract the record containing largest sequence_number for each user_id. So my table should look like this when it is all done:

User_ID sequence_num
22222222 150004
33333333 174101
66666666 220007

thanks!
j

 

[Norie]

Just create a totals query, group on User_ID and use max for sequence_num.

The SQL would look like this:

SELECT tblSeqNo.User_ID, Max(tblSeqNo.sequence_num) AS MaxOfsequence_num
FROM tblSeqNo
GROUP BY tblSeqNo.User_ID;

 

[jakemelon]

Hi,

I entered the sql and the results is just one record coming up with the highest sequence number. How do I get it work on all the records?

thanks

j

 

[Norie]

J

Try creating the query in the normal QBE design view.

Add the two fields User_ID and sequence_Num.

Right click in the grid and select Totals.

Then below sequence_Num select Max from the dropdown.

I copied your data into a table called tblSeqNo, did exactly that and got the result you posted.