Selecting 1 record from a group in a query

[turk39]

I have a table which contains records with these fields:

ID#
Name
Date
Ref#
etc.,etc.


There are multiple records for the ID# and they usually have
different dates.

The etc, etc fields may be different in many ways.

However, there are times when dates may be the same as well.
In this case, the reference numbers should be different.

I need to SELECT the one record for the ID with the most recent date. If there are 2 records with the same date I
need the one with the highest REF#.

Can I do this with only queries?

Thanks!

 

[dhouchin]

Yes, one way to accomplish it would be to use two queries like this:

[tt]
Query name: phase1
SELECT YourTable.id, Max(YourTable.date) AS fldDate
FROM YourTable
GROUP BY YourTable.id ORDER BY YourTable.id, Max (YourTable.date);

[/tt]
This will give you the latest date for each duplicate id. Then...
[tt]

Query name: phase2
SELECT YourTable.id, YourTable.date, Max(YourTable.ref) AS ref
FROM phase1 INNER JOIN YourTable ON (phase1.fldDate = YourTable.date) AND (phase1.id = YourTable.id) GROUP BY YourTable.id, YourTable.date;

[/tt]

...will give you the highest ref for the most latest dates, generated in phase 1.

After building these queries, all you will need to do is run phase2, and you will get the results that you're looking for.

Dennis

 

[turk39]

Thanks for your input. I was actually close to this method but I need to
go a bit further. I want to be able to get at the entire
record that has the latest date/ref. This is the part I don't know
how to do. How do I select all the other fields in the row for that record?



Test Table Data:

ID Name Date Ref Fld1 Fld2 Fld3 etc. -->
1 Mike 04/19/01 123 M 12 800
1 Mike 04/19/01 124 M 13 800
1 Mike 03/01/01 088 M 08 800
2 Mark 05/01/01 129 L 13 800
3 Bill 05/01/01 129 M 12 800
3 Bill 04/19/01 123 M 12 805
4 David 04/19/01 124 J 11 806
4 David 04/19/01 123 J 12 806
5 Leon 03/31/01 100 K 12 800



Phase1 Query to Find Last Date:

SELECT Test.ID, Max(Test.date) AS flddate
FROM Test
GROUP BY Test.ID
ORDER BY Test.ID, Max(Test.ID);

Results of Phase1:

ID flddate
1 04/19/01
2 05/01/01
3 05/01/01
4 04/19/01
5 03/31/01


Phase2 Query to get highest ref:

SELECT Test.ID, Test.Date, Max(Test.Ref) AS Ref
FROM TestLastDate INNER JOIN Test ON (TestLastDate.fldDate = Test.Date) AND (TestLastDate.ID = Test.ID)
GROUP BY Test.ID, Test.Date;


Results of Phase2:

ID Date Ref
1 04/19/01 124
2 05/01/01 129
3 05/01/01 129
4 04/19/01 124
5 03/31/01 100



This is good so far but I have many more fields in the record that I need
to get at. And it must be the most recent record.

Here is what I tried:

SELECT Test.ID, Test.Date, Max(Test.Ref) AS Ref, Test.Name, Test.fld1, Test.fld2, Test.fld3
FROM TestLastDate INNER JOIN Test ON (TestLastDate.fldDate = Test.Date) AND (TestLastDate.ID = Test.ID)
GROUP BY Test.ID, Test.Date;


I get the following error message:

You tried to execute a query that does not include the specified
expression 'Name' as part of an aggregate function.

 

[dhouchin]

Hi,

Just extend the concept a little further. Create a third query that uses phase2 to restrict the records from your table, giving you access to all of the other fields like this:

[tt]

Final query:
SELECT YourTable.id, YourTable.date, YourTable.ref, YourTable.name, YourTable.fld1, YourTable.fld2, YourTable.fld3 FROM YourTable

INNER JOIN phase2 ON (phase2.ref = YourTable.ref) AND (phase2.date = YourTable.date) AND (phase2.id = YourTable.id);

[/tt]

 

[turk39]

Worked Great!
Thanks so much for your help..