CODEWITHSUNDEEP
sql
Joel R.
Joel R.

Reputation: 189

SQL - How do I return the TOP 5 highest earning years?

I have a paystub table in which the payroll records are recorded.

The fields related to this query are the YearToDateGrossPay and the year.

I need to search through all the paystub records and select the top 5 highest paid years.

The last paystub of each year would tell me the final YTD amounts for that year and then I could select the top 5 of that subquery, but I'm not too sure how to do that.

Any help would be appreciated.

Thanks

Upvotes: 0

Views: 174

Answers (3)

ThierryT
ThierryT

Reputation: 191

Standard SQL:

SELECT * 
FROM final_ytd_amounts 
WHERE ROWNUM <= 5 
ORDER BY ytd_amount DESC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you are using SQL Server, the above won't work. In general, you should specify a particular database when asking a query. In SQL Server, you can do:

SELECT top 5 *
FROM final_ytd_amounts
ORDER BY ytd_amount DESC

For completeness, the following is how you do this in Oracle:

SELECT *
FROM final_ytd_amounts
where rownum <= 5
ORDER BY ytd_amount DESC

Upvotes: 0

vasek1
vasek1

Reputation: 14071

If you already have the subquery which selects the final YTD amounts, you can finish it off by

SELECT * FROM final_ytd_amounts ORDER BY ytd_amount DESC LIMIT 5

The ORDER BY ytd_amount DESC sorts the table by ytd_amount in descending order

The LIMIT 5 selects the top 5 rows only and chops everything else off

Upvotes: 2

Related Questions