July 26, 2006 at 3:53 pm
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
July 26, 2006 at 4:03 pm
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.
July 26, 2006 at 4:12 pm
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.
******************
July 27, 2006 at 1:35 pm
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?
July 27, 2006 at 2:01 pm
Yup!
I wasn't born stupid - I had to study.
July 27, 2006 at 2:03 pm
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
July 27, 2006 at 7:15 pm
Well, well, well... I wondered where I saw that lick of code before... Welcome aboard, Josh!
--Jeff Moden
July 28, 2006 at 10:43 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply