Select First and Second Highest Record in Database

The table is:

So far, I’ve found two ways to do this

TOP()

SELECT TOP(1) *
FROM (SELECT TOP(2) *
    FROM Salary
    ORDER BY Amount DESC) AS TOP2
ORDER BY Amount

MAX()

SELECT MAX(Amount)
FROM Salary
WHERE Amount Not In (SELECT MAX(Amount) FROM Salary)

I personally like the “TOP()” approach just because of its flexibility. If I want to select third, fourth, or fifth highest record, all I need to do is change the number in the second TOP() statement.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s