MS SQL equivalent of TOP n

[holdem]

I am new to DB2. Is there an equivalent to TOP n in MS SQL, to only return the TOP n records of a resultset?

 

[swampBoogie]

Look up the row_number function.

 

[holdem]

Thanks, But where do I find info on row_number. I did a search at IBM's info center and looked through the on-line SQL reference there and couldn't find it. Do you have a suggested source for DB2 SQL reference? I am 2 weeks into DB2 and need to build up my reference links and books.

Thanks

 

[swampBoogie]

The PDF file at this location

http://www.dsv.su.se/~mpe/DivDokument/DB2V70CK.PDF

contains some example of using row_number.

I also found this

http://www.student.math.uwaterloo.ca/~cs448/db2_doc/html/db2s0/db2s0551.htm

when doing a search on Google.

 

[gregsimpson]

holdem,

you will find the direct equivalent of TOP n is FETCH FIRST n ROWS.

Example

Select Cust_No, Order_No, Order_Qty
FROM
ORDER_TABLE
FETCH FIRST 10 ROWS ONLY.

Using the above predicate will ensure you only retrieve 10 rows.

Cheers
Greg

 

[blom0344]

Even more flexible is the use of the rank() or dense_rank()function , if you combine it with the 'partition over' phrase. This will allow you to rank data first and then limit the resultset by setting condition on the individual ranks.

rank() over (order by <field1>)

rank() over (partition by <field2> order by <field1>) T. Blom
Information analyst
tbl@shimano-eu.com