Clever Geek Handbook

SQL priority query

I have a logging table that has three columns. One column is a unique identifier. One column is called "Name" and the other is "Status".
The values ​​in the Name column can be repeated so that you can see the name "Joe" across multiple lines. The name "Joe" might have a line with the status "open", another line with the status "closed", another with "pending" and possibly one for "hold". I would like, using a certain priority in this highest and lowest order: (Closed, Hold, Pending, and Open) pulls the highest ranking string for each name and ignores the rest. Does anyone know an easy way to do this?

By the way, not every Name will have all status representations, so "Joe" may only have a "wait" and "hold" string, or maybe just "wait".

+1


source to share


3 answers


I would create a second table named "Status_Precedence" with lines like:

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


In another table query, connect to that table (in Status_Precedence.Status

) and then you can ORDER BY Status_Precedence.Order

.

+4


source


If you don't want to create another table, you can assign numeric priority using 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


The lookup table is also a good solution.

+4


source


I ended up with a matte solution b and took advantage of this final query to filter out the lower ranked (lower ranked 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]
0


source






More articles:


All Articles