×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Selecting 1 record from a group in a query

Selecting 1 record from a group in a query

Selecting 1 record from a group in a query

(OP)
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!

RE: Selecting 1 record from a group in a query

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


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


This will give you the latest date for each duplicate id. Then...


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;



...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

RE: Selecting 1 record from a group in a query

(OP)
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.





RE: Selecting 1 record from a group in a query

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:



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);

RE: Selecting 1 record from a group in a query

(OP)
Worked Great!
Thanks so much for your help..

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close