CODEWITHSUNDEEP
c#sql-serversorting
Developer
Developer

Reputation: 35

SQl sort data by multiple columns

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

Upvotes: 0

Views: 150

Answers (2)

Maxim Radchenko
Maxim Radchenko

Reputation: 21

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;

Upvotes: 2

vahab-balouchzahi
vahab-balouchzahi

Reputation: 247

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/

Upvotes: 0

Related Questions