Solved

Select by rownumber

Posted on 2013-05-10
8
289 Views
Last Modified: 2013-05-10
Hello,
I would like to find a record by rownumber. How do I do that?

DECLARE @TempTable table(int id)
INSERT INTO @TempTable(id)value(11)
INSERT INTO @TempTable(id)value(151)
INSERT INTO @TempTable(id)value(17)

I would like to select row 2, i.e. get 151
0
Comment
Question by:johnson1
8 Comments
 
LVL 6

Assisted Solution

by:BurundiLapp
BurundiLapp earned 125 total points
ID: 39155275
0
 
LVL 22

Assisted Solution

by:Om Prakash
Om Prakash earned 125 total points
ID: 39155289
This might work:

SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNum, ID FROM @TempTable) sub
WHERE RowNum = 2
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39155294
row_number() over ... applies to the retrieved rowset. You can't address the 2nd row ever inserted directly with it.
You will need a numeric column with the IDENTY property set. That column will auto-increment on each INSERT (only), and can then be selected for.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 7

Assisted Solution

by:Ross Turner
Ross Turner earned 250 total points
ID: 39155320
Here is an Example....

http://sqlfiddle.com/#!3/89cd0/10

Create Table TempTable 
(id int)

INSERT INTO TempTable(id)values(11);
INSERT INTO TempTable(id)values(151);
INSERT INTO TempTable(id)values(17);

Open in new window


Select 
* 
From 
(select 
ID,
ROW_NUMBER() over (order by (SELECT 1)) AS rownumber
from temptable
) as t
where rownumber = 2

Open in new window

0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39155339
The order of rows is not static, so that is not reliable. If there is an index, or even without, the order might change with later inserts.
0
 
LVL 7

Accepted Solution

by:
Ross Turner earned 250 total points
ID: 39155369
Wouldn't it make more sense to add an identity field

http://sqlfiddle.com/#!3/6a8bb/1


Create Table TempTable 
(ident int identity,
  id int)

INSERT INTO TempTable(id)values(11);
INSERT INTO TempTable(id)values(151);
INSERT INTO TempTable(id)values(17);

Open in new window


Select * from Temptable
where ident = 2

Open in new window

0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39155382
Ross,
see http:#a39155294 ;-).
0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39155387
lol yeah what you said :)
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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