retrieve n number of rows from table

[questionit]

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

[code green]

SELECT TOP 10 Names from Customers .... ORDER BY

[nbiswas]

Retrieve 'n' rows

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.

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.

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

SELECT A.RECORDS FROM (SELECT ROW_NUMBER() OVER (ORDER BY RECORDS) AS ROWID,RECORDS FROM CHOOSERECORDS) A 
WHERE A.ROWID BETWEEN 20 AND 30

Hope this answers your question.

[getrafi]

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

Thanks

[nbiswas]

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