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 c开发者_开发百科olumn value?

---

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

---

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

---

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!

---

oracle

select * from table where rownum < 5;

---

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.

---

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

---

......

MySQL

 select * from table order by field_name limit 0, 4 

MSSQL

 select top 5 from table order by field_name