Subject: SQL Query - MAX, Grouping and Sub query - Thread - go-pugleaf

Thread View: bit.databases.mssql-l

1 messages

[T] Back to Threads [V] Tree View [G] Group Articles

1 total messages Started by Phil Tue, 24 Sep 2002 10:06

Subject: SQL Query - MAX, Grouping and Sub query

#204

Author: Phil
Date: Tue, 24 Sep 2002 10:06

38 lines
2113 bytes

Hi

I am tryring to extract the most recent record from a table for a given ID
number.

i.e.

mainID        subID        Date_updated        Details......

20                 101        10/02/2002           ...........
20                 101        14/04/2002           ...........
20                 102        15/03/2002           ............
20                 102        15/06/2002            ................

I thought I could use grouping and MAX function

i.e.

SELECT max(date_updated) as MaxDate, subID, policyID
WHERE mainID = 20
GROUP BY subID

this returns

ID        subID        Date_created

20        101           14/04/2002
20        102           15/06/2002

this is good, however I need to return the other details in the table. By
selecting more fields to display it does not work as it wants to group them.
By putting this query into a sub query you are unable to do so as it returns
2 fields Date_created (for the max date thing) and Benefit ID which is what
i want to display the record by.

Any ideas anyone?

TIA Phil

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads