Row count

Question

Post reply

Row count

Susan-322874

SSCrazy

Points: 2300
More actions
July 26, 2006 at 3:53 pm

#113900

A developer asked me if there is any way to pull data from a table by actual row numbers.
He wants to select row 2 (apple) by using a rowid.. I know Oracle uses rowID, could we use identityfield?
Table - Fruit
Orange
Apple
Peach

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Is this what you want?

DECLARE @Table TABLE( ID integer IDENTITY, Fruit varchar(10))

INSERT INTO @Table

SELECT 'Orange' UNION ALL

SELECT 'Apple' UNION ALL

SELECT 'Peach'

SELECT * FROM @Table

SELECT * FROM @Table WHERE ID = 2

I wasn't born stupid - I had to study.

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 2

Please keep in mind that there is no specific order in which SQL stores data in the table. Farrell's solution will work if you insert all the rows manyally. You could do a bulk insert or SELECT INTO... but the order in which they will be inserted may not be the same in which they are in your current table. So asking for a specific row doesnt make much sense unless you have specific conditions like an ORDER BY or some WHERE clauses.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Bill Scott SSCrazy Points: 2338 More actions · Answer 3

Agreed, if your programmer wants to refer to the row that contains 'apple' then he should use that in his where clause. What is he doing that he thinks he needs a row number to process with?

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

Farrell Keough

SSCoach

Points: 17414

July 27, 2006 at 2:01 pm

#652006

Yup!

I wasn't born stupid - I had to study.

Josh Biegel SSC Rookie Points: 27 More actions · Answer 5

SG,

I wonder if what you're looking for is to be able to pull up a page of information at a time. Like if you have a table of threads and replies, you may want to pull up a page of the replies at a time, say reply 51 - 100.

If that's your goal, I have some code here that I think will help you.

My example will get between the 51st and 100th rows:

--Delete Temporary table if it exists

IF OBJECT_ID('TempDB..#Tally') IS NOT NULL

DROP TABLE #Tally

--Create Temporary table

SELECT TOP 9999

IDENTITY(INT,1,1) AS N

INTO #Tally

FROM Master.dbo.SysComments sc1,

Master.dbo.SysComments sc2

--Add a Primary Key to the table

ALTER TABLE #Tally

ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)

--Declare variables

DECLARE @StartRow AS INT

DECLARE @EndRow AS INT

DECLARE @Difference AS INT

DECLARE @Query AS VARCHAR(8000)

SET @StartRow = 51

SET @EndRow = 100

SET @Difference = @EndRow - @StartRow + 1 -- + 1 To include the starting Row Leave it off to preclude it.

--Create the Query Statement

SET @Query = '

SELECT *

FROM

(SELECT TOP ' + CONVERT(VARCHAR(10),@Difference) + ' N

FROM

(SELECT TOP ' + CONVERT(VARCHAR(10),@EndRow) + ' N

FROM #Tally

ORDER BY N ASC) myTable

ORDER BY N DESC) myTable2

ORDER BY N ASC'

--Check the Query Statement

SELECT @Query

--Execute the Query

EXEC (@Query)

The extremely important point here is to get your ORDER BY sections right. They're the whole key to this problem.

You may wonder why I create a Query string and insert the values into it. This is because if you have a version of Microsoft SQL Server earlier than 2005 (Like I do), 'SELECT TOP @myVariable' is not allowed. Your TOP keyword has to be for a set number or percentage, not a varialble.

If you do have SQL Server 2005, you can make the alteration to simply run the query as you would normally instead of building and executing a query inside a query.

Good luck,

Josh

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 6

Well, well, well... I wondered where I saw that lick of code before... Welcome aboard, Josh!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Josh Biegel SSC Rookie Points: 27 More actions · Answer 7

Well Jeff, you can't tell people to come take a gander at this place over and over again without them eventually doing it. So here I am.

I'll probabaly just lurk a lot though.

Good luck,

Josh