How to select top N from a table

Question

Now I am working on a project, which has to select top 25 records from a table according to one column, let's call it Num.

The case is 1) the table is not sorted by Num;

I know this part can be done by using "GROUP ORDER BY"

2) the number of the records in the table might be less than 25.

For this question, is there any way to make it in just one single SQL statement?

Thanks so much!

Answer 1

SELECT ...
  LIMIT 25

Answer 2

select top 25 *
from your_table
order by Num asc

On SQL Server that would select the 25 first records starting from the lowest value of Num. If you need the highest, use "desc" instead of "asc".

Answer 3

Depending on the database implementation you're using, it could be using the limit statement (as one other answer suggests) or it could be like this:

SELECT TOP 25 Num, blah, blah ...

Answer 4

Not sure I understand the requirement, but you can do:

SELECT TOP 25 Num FROM Blah WHERE Num = 'MyCondition'

If there aren't 25 records, you won't get 25. You can perform an ORDER BY and the TOP will listen to that.

Answer 5

Select Top 25 [Column] From [Table] Order By [Column]

If you have fewer than 25 records, this will just pull however many there are.

Answer 6

For SQL Server:

select top 25 * from table order by Num asc

For mySQL:

select * from table order by Num asc limit 25

Answer 7

It depends heavily on your database, as there is no standard way to do this.

In SQL Server, for example, I have used Row_Number (http://msdn.microsoft.com/en-us/library/ms186734.aspx) to do this, so I can select which group I was interested in (for paging, for example), but Top also works.

For Oracle you can look at rownum (http://www.adp-gmbh.ch/ora/sql/examples/first_rows.html).

And MySQL has been mentioned already.

Answer 8

Oracle:

Select *
FROM Table
WHERE rownum <= 25

MSSQL:

SELECT TOP 25 * 
from Table

Mysql:

SELECT * FROM table
LIMIT 25