Select by rownumber

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
johnson1Asked:
Who is Participating?
 
Ross TurnerConnect With a Mentor Management Information Support AnalystCommented:
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
 
BurundiLappConnect With a Mentor Commented:
0
 
Om PrakashConnect With a Mentor Commented:
This might work:

SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNum, ID FROM @TempTable) sub
WHERE RowNum = 2
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
Ross TurnerConnect With a Mentor Management Information Support AnalystCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Ross,
see http:#a39155294 ;-).
0
 
Ross TurnerManagement Information Support AnalystCommented:
lol yeah what you said :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.