By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

retrieve n number of rows from table

100+
P: 553
How do i retreive 10 rows from a table using SQL

SELECT Names from Customers... how do i change it to retrieve only 'n' number of rows?

Please suggest an easy method
Thanks
Jun 22 '09 #1
Share this Question
Share on Google+
4 Replies


code green
Expert 100+
P: 1,726
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 10 Names from Customers .... ORDER BY
Jun 22 '09 #2

nbiswas
100+
P: 149
CASE 1

If you want to retrieve only the first 10 or 20 rows, then the go with TOP N statement.

Where N indicates any number

e.g.

Expand|Select|Wrap|Line Numbers
  1. Select TOP 10 * from table_name
CASE 2
If you want to retrieve only the last 10 or 20 rows, then the go with TOP N .. ORDER BY DESCstatement

e.g.
Expand|Select|Wrap|Line Numbers
  1. Select TOP N * from table_name ORDER BY COLUMN_NAME DESC
CASE 3

If you want to retrieve the records in between(say 20th to 30th out of 100 records).

Use ROW_NUMBER() function.

I have a table(say CHOOSERECORDS) with only 1 column(say RECORDS) of type int

Now I have inserted values from 1 to 100 there.

Next I want to select records from say 20 to 30. The query is here

Expand|Select|Wrap|Line Numbers
  1. SELECT A.RECORDS FROM (SELECT ROW_NUMBER() OVER (ORDER BY RECORDS) AS ROWID,RECORDS FROM CHOOSERECORDS) A 
  2. WHERE A.ROWID BETWEEN 20 AND 30
Hope this answers your question.
Jun 26 '09 #3

P: 1
Thanks biswas,
But i am using joins. how can i use this logic with joins

Thanks
Mar 15 '10 #4

nbiswas
100+
P: 149
Say for example

Select Name , Address From(
Select Row_Number() Over(Order by Name) RowId, Name,Address
From(
Select t1.Name, t2.Address from tbl t1
Join tbl t2 On t1.Id = t2.Id)x)A
Where A.ROWID BETWEEN 20 AND 30

Hope this helps
Mar 25 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.