CODEWITHSUNDEEP
sqlms-access
user3219632
user3219632

Reputation: 171

MS Access: Selecting the first item according to a rank

Imagine I have a query called QueryA that returns stuff like this:

Employee    Description   Rank
John        Happy         1
John        Depressed     3
James       Happy         1
James       Confused      2
Mark        Depressed     3

I am trying to make a query that grabs the Employee and the Description, but only one description -- the one with the best "rank." (the lower the rank the better). I sort QueryA by Employee then by Rank (descending).

So I'd want my new query QueryB to show that John as Happy, James as Happy, and Mark as Depressed.

However I try selecting Employee and then First of Description and it doesn't always work.

Upvotes: 1

Views: 45

Answers (1)

crthompson
crthompson

Reputation: 15875

I'm not able to check this for Access, but it should work fine. Check my SQL Fiddle

select
  r.employee, d.description
from
  table1 as d
  inner join (select min(rank) as rank, employee
              from
                 table1
              group by employee) r on d.rank = r.rank
                                      and d.employee = r.employee

Upvotes: 1

Related Questions