Limit SQL query to 5 points

Question

How to write a simple SELECT element that restricts the report to only the top 5 column value?

Answer 1

You will have to sort by this column value, possibly in descending order, depending on what you mean by "top 5"; and fetching only the top 5 rows.

Using MySQL, you will have something like this, I would say:

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

Using the MSSQL server, you have no limit , but you can use top :

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

Answer 2

Don’t forget SQL Server WITH TIES. If the top 6 values ​​match, then the top 5 will be randomly selected from 6

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

Answer 3

Sybase

Return the first n lines.

 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 restriction to multiple queries with set rowcount

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

MySQL

MySQL Docs

Return the first n lines, starting at line 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 lines starting at line 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 lines starting at line m

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

---

If I missed any databases or row restriction methods, post a comment and I will add it. Thanks!

Answer 4

oracle

select * from the table where rownum <5;

Answer 5

......

MySQL

  select * from table order by field_name limit 0, 4 

MSSQL

  select top 5 from table order by field_name 

Answer 6

 SELECT TOP 5 LastName FROM Employees ORDER BY LastName 

You must use order if you want to arrange them. If you need them in descending order (high to low), add DESC at the end of the query.

Answer 7

Do you mean something like (in MSSQL) ?:

 SELECT DISTINCT TOP 5 column_name FROM table_name ORDER BY column_name 

This will select only the column you are interested in, and he will be sure that he will not duplicate the values. If you want to get the top 5, regardless of whether they are the same or not, you should try it without a clear description.

 SELECT TOP 5 column_name FROM table_name ORDER BY column_name