Search code examples
sqlsql-serversql-order-bywhere-clausesql-limit

MSSQL query based on column priority

![enter image description here][1]

my MSSQL table has these properties - id1, id2, isVerified, isRejected, score1, score2, score3. I want to show top1 value where isveried == 1, isRejected==0. After that my first priority is score1. If score1 ties with any other, then 2nd priority is score2, if score2 ties, 3rd priority is score3. The higher the number, the higher the priority. what will be the query? please help.

Solution

  • I suspect that you want:

    select top (1) *
from mytable
where isverified = 1 and isrejected = 0
order by score1 desc, score2 desc, score3 desc

    The query filters on verified and non-rejected rows. Then, we keep the row that has the highest score1; if there are ties, we compare score2, and then score3.