CODEWITHSUNDEEP
sql
Trevor
Trevor

Reputation: 235

Limiting SQL Statement to top 5 amounts

How do I write a simple SELECT statment which limits the report to only the top 5 of a column value?

Upvotes: 5

Views: 2506

Answers (7)

Sarfraz
Sarfraz

Reputation: 382726

......

MySQL

 select * from table order by field_name limit 0, 4

MSSQL

 select top 5 from table order by field_name

Upvotes: 0

Eric Johnson
Eric Johnson

Reputation: 17970

Sybase

Return the first n rows. 

select top n * from table order by column

This does not work because top is a keyword not a function:

select top(n) * from table order by column

Apply a limit to multiple queries using set rowcount

set rowcount n
select * from table order by column
...
set rowcount 0

MySQL

MySQL docs

Return the first n rows starting with row m.

select * from table limit m, n order by column

SQL Server

Return the first n rows

select top n from table order by column

Oracle

Return the first n rows

select * 
  from table 
 where rownum < 5 
 order by column

SQLite

SQLite docs

Return the first n rows

select * from table order by column limit(n)

Return the first n rows starting with row m

select * from table order by column limit(m,n)

Return the first n rows

select * from table order by column limit(n) offset(m)

Postgres

Postgres docs

Return the first n rows

select * from table order by column limit(n)

Return the first n rows starting with row m

select * from table order by column limit(n) offset(m)

If I missed any databases or row limiting methods please post a comment and I'll add it. Thanks!

Upvotes: 2

ziggy
ziggy

Reputation: 15876

oracle

select * from table where rownum < 5;

Upvotes: 1

gbn
gbn

Reputation: 432271

Let's not forget the SQL Server WITH TIES. If the top 6 values are the same, then the top 5 will be chosen randomly from the 6

SELECT TOP 5 WITH TIES... ORDER BY col DESC

Upvotes: 3

Pascal MARTIN
Pascal MARTIN

Reputation: 401022

You'd have to sort by that column value, maybe in descending order, depending on what you mean by "top 5" ; and fetching only the 5 top lines.

Using MySQL, you'd have something like this, I'd say :

select *
from your_table
where ...
order by your_column desc
limit 5

Using MSSQL server, you don't have limit, but you could use top :

select top 5 *
from your_table
where ...
order by your_column desc

Upvotes: 4

Amadiere
Amadiere

Reputation: 11416

You mean something like (in MSSQL)?:

SELECT DISTINCT TOP 5 column_name FROM table_name ORDER BY column_name

This would select only the column you were interested in, and it would make sure it wouldn't duplicate the values. If you'd like the top 5, irregardless of whether they are the same or not, you should try it without the distinct.

SELECT TOP 5 column_name FROM table_name ORDER BY column_name

Upvotes: 0

Brent Ozar
Brent Ozar

Reputation: 13274

SELECT TOP 5 LastName
FROM Employees
ORDER BY LastName

You have to use the order-by if you want to get them in order. If you need them in descending order (high to low) add DESC to the end of the query.

Upvotes: 0

Related Questions