Select From Group?

chris_jones199 · September 19th, 2005, 07:15 AM

Hi Guys, can anyone help me with this please?

I have some data in the form:

Code:

ID      Sequence   Field1   Field2
1234       1         x         y
1234       2         x         z
1234       3         a         q
5432       1         q         w
5432       2         a         w
5432       3         e         e

How can I group the above records by ID and select the records with the largest sequence number in each group to give:

Code:

ID      Sequence  Field1   Field2
1234       3       a         q
5432       3       e         e

I can't use the GROUP BY or DISTINCT clauses as this will aggregate the records in each group, which is not an option.

Any ideas?

Thanks in advance,

Chris

Jeff Mason · September 19th, 2005, 12:28 PM

You can do this using a (correlated) subquery. It's best to work these from the "inside out". First create a query which returns the Sequence number you want (the maximum) for any given ID:

Code:

    SELECT MAX(Sequence) FROM YourTable WHERE ID = <a given ID>

Then, you want to select the row, in each subset of rows for a given ID, that has this Sequence (in pseudocode):

Code:

    SELECT ID, Sequence, Field1, Field2
    FROM YourTable
    WHERE ID = <the ID I'm currently on>
        AND <the Sequence is a maximum>

Combine the two and correlate the two queries on the ID:

Code:

    SELECT ID, Sequence, Field1, Field2
    FROM YourTable T1
    WHERE Sequence = SELECT MAX(Sequence) FROM YourTable T2 WHERE T1.ID = T2.ID

...should do the trick.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

chris_jones199 · September 21st, 2005, 06:33 AM

That's great.

Thank you Jeff.

Cheers,

Chris

iverson_wuwei · October 12th, 2005, 12:05 AM

select ID,max(sequence),field1,field2 from table
group by id
order by id;

Jeff Mason · October 12th, 2005, 05:08 AM

Quote:

quote:Originally posted by iverson_wuwei
select ID,max(sequence),field1,field2 from table
group by id
order by id;

And if you try that, you'll get an error, since that query is not valid SQL.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com