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. 🤗🤗