SQL for Healthcare: Retrieving the Second and Third Highest Values without the LIMIT statement
Hey there data nerds.
Welcome to a new SQL week. 😁
So, as we all know, if you’d like to retrieve the highest/largest value in a column, you use the MAX keyword. E.g. to get the highest product price =
SELECT MAX(Price)
FROM Products
But what if you’d like to get the second or/and the third highest values? For those who make use of Microsoft SQL Server (like me), you’ll know that one cannot make use of the LIMIT keyword in SQL Server.
To answer this, I made use of my ‘Patients’ dataset, retrieving the second and third highest healthcare expenses.
To get the highest healthcare expense:
SELECT MAX(Healthcare_Expenses)
FROM Patients
For the 2nd highest expense:
SELECT MAX(Healthcare_Expenses)
FROM Patients
WHERE Healthcare_Expenses <>
(SELECT MAX(Healthcare_Expenses)
FROM Patients)
Then for the 3rd, I made use of three different methods:
Using the TOP keyword
SELECT TOP 1 Healthcare_Expenses
FROM
(SELECT TOP 3 Healthcare_Expenses
FROM Patients
ORDER BY Healthcare_Expenses DESC) kl
ORDER BY Healthcare_Expenses
OR
Using the OFFSET and FETCH clauses
SELECT Healthcare_Expenses
FROM Patients
ORDER BY Healthcare_Expenses DESC
OFFSET 2 ROWS
FETCH NEXT 1 ROW ONLY
OR
Using NOT IN clause/nested queries
SELECT MAX(Healthcare_Expenses) Third
FROM Patients
WHERE Healthcare_Expenses <
(SELECT MAX(Healthcare_Expenses)
FROM Patients
WHERE Healthcare_Expenses NOT IN
(SELECT MAX(Healthcare_Expenses)
FROM Patients))
I hope you learned something new from this. Make sure to check out my full SQL for Healthcare article on a Hospital database which answers several business questions concerning the patients. Have a lovely SQL week. 🤗🤗