6

I have to select the top 25 records from a table according to the column Num.

There are two issues. First, the table is not sorted by Num. I know this can be resolved by using GROUP ORDER BY. Second, the number of the records in the table might be less than 25.

Is there any way to perform this selection in just one SQL statement?

Pops
  • 30,199
  • 37
  • 136
  • 151
skydoor
  • 25,218
  • 52
  • 147
  • 201
  • 1
    How you do this particular task is largely dependent on your specific RDBMS, as you can already see by some of the differing solutions below. You should always try to specify that in your question if it's appropriate (i.e., not a "general" or ANSI standard SQL question) – Tom H Jul 28 '10 at 13:50
  • 3
    I have to nitpick this because it's an important concept... in SQL tables do not have an order. The particular server may store them in a specific order on disk (or they may not), but if you want ordering in your results you should ALWAYS use the ORDER BY clause (which is different from the GROUP BY clause). As far as I know, there is no GROUP ORDER BY clause in any RDBMS that I've used. – Tom H Jul 28 '10 at 13:52
  • 1
    the accepted answer to [...this question...](http://stackoverflow.com/questions/595123/is-there-an-ansi-sql-alternative-to-the-mysql-limit-keyword) lists ways to do this for DB2, Informix, SQL Server, Access, MySQL, PostgreSQL, and Oracle – KM. Jul 28 '10 at 14:02

9 Answers9

15

For SQL Server:

select top 25 * from table order by Num asc

For mySQL:

select * from table order by Num asc limit 25
Sarfraz
  • 377,238
  • 77
  • 533
  • 578
3

Oracle:

Select *
FROM Table
WHERE rownum <= 25

MSSQL:

SELECT TOP 25 * 
from Table

Mysql:

SELECT * FROM table
LIMIT 25
Michael Pakhantsov
  • 24,855
  • 6
  • 60
  • 59
1
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".

Valentino Vranken
  • 5,597
  • 1
  • 26
  • 28
1

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 ...
codykrieger
  • 1,750
  • 15
  • 19
1

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.

James Black
  • 41,583
  • 10
  • 86
  • 166
0
SELECT ...
  LIMIT 25
Ignacio Vazquez-Abrams
  • 776,304
  • 153
  • 1,341
  • 1,358
0

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.

Adam Houldsworth
  • 63,413
  • 11
  • 150
  • 187
0

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

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

AllenG
  • 8,112
  • 29
  • 40
0

In Firebird,

select first 25 
from your_table
order by whatever
No'am Newman
  • 6,395
  • 5
  • 38
  • 50