find first record in table where criteria are met

[VLR]

name, rank, date

grouped by name, sorted by date

I want to find the first record where rank is <criteria.

hope this isn't too vague

thanks

 

[Tom Ellison]

Dear Malabor:

This can be done with a correlated subquery:

SELECT name, rank, date
FROM YourTable T
WHERE rank < 20 AND date =
(SELECT MIN(date) FROM YourTable T1
WHERE T1.name = T.name AND T1.rank < 20)

Substitute the actual name of your table.

I assume you want to see separate result for each name because you
said to group by name. Next, I assume that by "first record" you
meant the one with the earliest date.

If there are two rows with the same name and date which meet the
criteria of having a low rank and the earliest date, then you will see
both. If this is a problem, you must affirmatively select some method
of choosing between them. Otherwise, the selection criteria are
ambiguous in this respect.

Naming columns with reserved words or function names will probably
catch up with you. Try to use column names other than "name" and
"date". We see many questions in this newsgroup where the problem
would have been avoided by "UserName" and "DepositDate" (or some other
appropriately selected more descriptive column name).

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts