asked on

select the nth row of a table

how can i easily to select the nth row of a table in SQL query.

For example, if i want to select the 3rd of a table, how can i do that?
(notes: if the table does not have row number)

thombeck

you can use this code:

SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
columns
FROM tablename
) AS foo
WHERE rownumber <= n

Jared_S

use row_number()
post some details of your data and we can build the query.

James Murrell

SQL 2005 and above has this feature built-in. Use the ROW_NUMBER() function. It is excellent for web-pages with a << Prev and Next >> style browsing:

Syntax:

SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY MyColumnToOrderBy) AS RowNum, * FROM Table_1) sub
WHERE RowNum = 23

Taken fromhttp://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table

I use it all the time: had link bookmarked

Dave Baldwin

@cs97jjm3's method is excellent, I use it myself for pagination. But... SQL tables do not have built-in 'row numbers' like spreadsheets do. ROW_NUMBER() Depends on the ORDER BY statement for it's order. If you insert a value that changes the ORDER BY, you will change the Row Number for a particular item.

hongclub

ASKER

i used that too, but cuz in my sql has select distinct, so when i use the ROW_NUMBER, it return more rows than i though

select Distinct col1, ROW_NUMBER() Over(Order By col1), col2from #tempResult order by col2 asc

ralmada

maybe you should try with dense_rank

dense_rank() Over(Order By col1)

More info on dense_rank here:

http://msdn.microsoft.com/en-us/library/ms173825.aspx

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alpesh Patel

In 2005/2008

SELECT col1,
col2,
ROW_NUMBER() OVER (ORDER BY col1) Row
FROM (SELECT DISTINCT
col1,
col2
FROM #tempResult
) d
WHERE Row = 3
ORDER BY col2 ASC

As Above solutions provided by other experts

In SQL Server 2012

select * from sys.objects where modify_date > format(DateAdd(DAy, -1, getdate()),'yyyyMMdd')
order by name
Offset 10 rows Fetch Next 1 rows only

Anthony Perkins

To clarify: The comment awarded points is an exact copy and paste of the solution I posted previously here http:#a38761410

Anthony Perkins

Please leave your recommendations here as to how you wish to see this question closed.
3) http:#a38761410