SQl sort data by multiple columns

Question

I need to sort the following table from the query:

SELECT *
FROM Likelihood WITH (NOLOCK) 
INNER JOIN Diagnosis ON Diagnosis.DiagnosisID = Likelihood.DiagnosisID
WHERE Likelihood.SessionID = (6768) 
ORDER BY Likelihood.Percentage DESC

    **Percentage   Diagnosis   Level**
    100            F43.10      HIGH
    83.333336      F84.5       HIGH
    75             F40.9       HIGH
    66.666664      F90.0       MEDIUM
    50             F51.09      MEDIUM

First sort by Likelihood.Percentage in descending.

Then when Level = 'HIGH' then sort by Diagnosis ascending. Diagnosis column is a string.

When Level = 'MEDIUM' then sort by Diagnosis ascending

The resulting table should be:

**Percentage   Diagnosis   Level**
100            F43.10      HIGH
75             F40.9       HIGH
83.333336      F84.5       HIGH
50             F51.09      MEDIUM
66.666664      F90.0       MEDIUM

I tried this query, but did not get the results:

SELECT *
FROM Likelihood WITH (NOLOCK) 
INNER JOIN Diagnosis ON Diagnosis.DiagnosisID = Likelihood.DiagnosisID
WHERE Likelihood.SessionID = (6768) 
ORDER BY 
    case when Level='HIGH' and Percentage > 70 then Diagnosis.Diagnosis end
    , case when Level='Medium' and Percentage between 50 and 69 then Diagnosis.Diagnosis end
    , Likelihood.Percentage DESC

Unrelated comment: It's REALLY bad practice to just throw in "WITH (NOLOCK)" like that. Never use that unless you absolutely, positively HAVE to and have no other recourse. It's just a really bad habit to get into. — pmbAustin
– pmbAustin, Commented May 8, 2020 at 21:31
Your statement of how you want to sort makes no sense to me. You say FIRST sort by percentage... well, each percentage is unique so there's no way a secondary sort criteria will ever come into play. And your example 'results' shows that you're FIRST sorting by 'Level" (High = 1st, Medium = 2nd, presumably anything else = 3rd). So what you're really saying is ORDER BY Level ASC, Diagnosis DESC. If you don't mean that, then your question needs to be updated, because I find your statement of the problem to be ambiguous at best. — pmbAustin
– pmbAustin, Commented May 8, 2020 at 21:34

Maxim Radchenko · Accepted Answer · 2020-05-08 21:34:22Z

2

If I understood correctly, you simply need to specify a few order by conditions. First is Percentage, then Level (HIGH should be before the MEDIUM because of alphabetic sorting), and then by Diagnosis. So,

order by 
    Percentage DESC,
    Level ASC, 
    Diagnosis ASC;

answered May 8, 2020 at 21:34

Maxim Radchenko

212 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vahab-balouchzahi · Accepted Answer · 2020-05-08 21:29:34Z

0

it is an example for multi column sort :

SELECT city,first_name,last_name
 FROM
sales.customers
ORDER BY
city DESC,
first_name ASC;

for more guide check below link

https://www.sqlservertutorial.net/sql-server-basics/sql-server-order-by/

answered May 8, 2020 at 21:29

vahab-balouchzahi

2472 silver badges10 bronze badges

Collectives™ on Stack Overflow

SQl sort data by multiple columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related