Solved

select the nth row of a table

Posted on 2013-01-09
12
476 Views
Last Modified: 2013-01-13
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)
0
Comment
Question by:hongclub
12 Comments
 
LVL 2

Expert Comment

by:thombeck
ID: 38760423
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
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38760429
use row_number()
post some details of your data and we can build the query.
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 38760434
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
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 38760457
@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.
0
 

Author Comment

by:hongclub
ID: 38760463
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
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 41

Expert Comment

by:ralmada
ID: 38760505
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
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 38761410
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
Try it this way:
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

Open in new window

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38761785
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38765861
To clarify:  The comment awarded points is an exact copy and paste of the solution I posted previously here http:#a38761410
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38769175
Please leave your recommendations here as to how you wish to see this question closed.
3) http:#a38761410
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now