SQL Precedence Query

Question

I have a logging table which has three columns. One column is a unique identifier, One Column is called "Name" and the other is "Status".
Values in the Name column can repeat so that you might see Name "Joe" in multiple rows. Name "Joe" might have a row with a status "open", another row with a status "closed", another with "waiting" and maybe one for "hold". I would like to, using a defined precedence in this highest to lowest order:("Closed","Hold","Waiting" and "Open") pull the highest ranking row for each Name and ignore the others. Anyone know a simple way to do this?

BTW, not every Name will have all status representations, so "Joe" might only have a row for "waiting" and "hold", or maybe just "waiting".

Answer 1

I would create a second table named something like "Status_Precedence", with rows like:

Status  | Order
---------------
Closed  |  1
Hold    |  2
Waiting |  3
Open    |  4

In your query of the other table, do a join to this table (on Status_Precedence.Status) and then you can ORDER BY Status_Precedence.Order.

Answer 2

If you don't want to create another table, you can assign numeric precedence using a SELECT CASE

Select Name, Status, Case Status 
     When 'Closed' then 1
     When 'Hold' then 2
     When 'Waiting' then 3
     When 'Open' Then 4
     END
      as StatusID

      From Logging
Order By StatusId -- Order based on Case

A lookup table is also a good solution though.

Answer 3

I ended up using matt b's solution and using this final query to filter out the lower ranked (lower bing higher numbered).

SELECT * from [TABLE] tb
LEFT JOIN Status_Precedence sp ON tb.Status = sp.Status
WHERE  sp.Rank = (SELECT MIN(sp2.rank)
                FROM[Table] tb2
           LEFT JOIN Status_Precedence sp2 ON tb2.Status = sp2.Status
                WHERE tb.Status = tb2.Status)
order by tb.[name]