SQL Query - MAX, Grouping and Sub query 
Author Message
 SQL Query - MAX, Grouping and Sub query

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



Sat, 12 Mar 2005 18:04:22 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Using Sub Query in Sql Query - Crystal Report 5.0

2. how can I build 2 queries in 1 query(VB SQL query)

3. SQL Help - SQL sub-query

4. Using MAX in SQL SERVER query

5. Using MAX in SQL SERVER query

6. Max number of fields in an ADO/Sql 6.5 query

7. SQL Sub-queries

8. Q: Pass a SQL query from VB to crystal report sub-report

9. Formula in a SQL Sub Query

10. How to change SQL query in Sub-report

11. Sub SQL Query

12. SQL Query / Grouping

 

 
Powered by phpBB® Forum Software