January 25, 2008 at 1:16 pm
I have a table which contains information on approx 250 people. There are 3 columns in this table that I'm interested in (ID, LName, FName). What I'm looking to do is write a SP that I can pass an ID to, and have it return the ID, LName, and FName of 2 people, the one previous to that record, and the next record when sorted alphabetically.
Here's what I have so far.
----------------------------------------------------------------------------------
DECLARE @ID int
DECLARE c1 SCROLL CURSOR
FOR
SELECT ID
FROM Players
WHERE Pro = 1
ORDER BY LName, FName
OPEN c1
FETCH NEXT FROM c1 INTO @ID
WHILE @ID <> 10069
BEGIN
FETCH NEXT FROM c1 INTO @ID
END
-- Find previous player
FETCH PRIOR FROM c1 INTO @ID
SELECT (LName + ', ' + FName) FROM Players WHERE ID = @ID
-- Find next player
FETCH NEXT FROM c1 INTO @ID
FETCH NEXT FROM c1 INTO @ID
SELECT (LName + ', ' + FName) FROM Players WHERE ID = @ID
CLOSE c1
DEALLOCATE c1
----------------------------------------------------------------------------------
Thanks in advance for your help.
Stephen
January 26, 2008 at 12:32 pm
I have a table which contains information on approx 250 people. There are 3 columns in this table that I'm interested in (ID, LName, FName). What I'm looking to do is write a SP that I can pass an ID to, and have it return the ID, LName, and FName of 2 people, the one previous to that record, and the next record when sorted alphabetically.
assuming you want don't want the record that matches the given ID, this should work:
declare @Name varchar(200)
select @Name = LName + FName from Players where ID = @ID
select top (1) ID, LName, FName
into #prior
from Players
where (LName+FName) < @Name
order by LName desc, FName desc
select top (1) ID, LName, FName
into #next
from Players
where (LName+FName) > @Name
order by LName asc, FName asc
select ID, LName, FName from #prior
UNION
select ID, LName, FName from #next
January 26, 2008 at 5:30 pm
As long as you're using SQL 2005 there are two things that could solve this: Common Table Expressions and the ROW_NUMBER function.
ALTER PROC usp_ShowPreviousNext( @myid INT)
AS
WITH myResults AS
(SELECT id, fname, lname, row_number() OVER (ORDER BY lname,fname) AS rownumber FROM player)
SELECT id,fname,lname,rownumber FROM myResults
WHERE rownumber = (SELECT rownumber - 1 FROM myResults WHERE id = @myid)
OR rownumber = (SELECT rownumber + 1 FROM myResults WHERE id = @myid)
January 26, 2008 at 5:51 pm
i've read that the drawback of row_number() is that it must be calculated for the entire partition of the over() clause and thus usually results in higher io. anyone have any opinions/feedback on this?
January 27, 2008 at 5:56 pm
Based on the original post it's unlikely that IO would have much impact on performance; 250 rows in one table that has 3 columns would likely fit on 2 or 3 pages.
More important than performance are correct results. The solution that uses temp tables will cause incorrect results to be returned in certain cases.
For example, using this list of names, take Robert Brown as the person with the search ID.
Jo Brown
Robert Brown
Steven Brown
Mary Browning
Bridget Browqett
From this list you'd expect Jo Brown and Steven Brown to be returned as the previous and next entries.
However, the example using temp tables returns Mary Browning and Steven Brown. But Mary Browning clearly comes after Robert Brown so it appears that something unexpected is occurring. To see what that is here's the list of concatenated lname+fname rows.
BrownJo
BrownRobert
BrownSteven
BrowningMary
BrowqettBridget
Now sorted in ascending alphabetical order.
BrowningMary
BrownJo
BrownRobert
BrownSteven
BrowqettBridget
This shows that 'BrowningMary' < 'BrownRobert' is true which puts it into #prior, then when #prior is sorted Mary Browning is placed on the top of the list. Subsequently the SELECT TOP (1) returns Mary, not Jo.
January 28, 2008 at 8:04 am
hey todd. thanks for the feedback. when i give a suggestion i don't always put on my qa hat as i just want to help someone down the road but not drive them home. also, i would never sort or order by an expression. i probably would have done the compare by saving the anchor row either into a variable or two or most likely a temp table. but i didn't want to confuse the original poster too much since they're new.
now back to my question (which i should have posted in a new thread). i know that for 250 rows, i/o is inconsequential, but once you get into large tables what's your opinion on the use of row_number()?
January 28, 2008 at 6:20 pm
Hi Antonio,
I thought the comparison in Inside Microsoft SQL Server 2005: T-SQL Querying (p.241) that shows a benchmark between some different methods was interesting. In each case the ROW_NUMBER function completed faster and didn't take significantly longer as the number of rows increased.
Other than performance there are two things I like about it. One, it can be a more concise way to write a query. Two, row number assignments can easily be changed since they're based on the ORDER BY clause.
It's my understanding the there's going to be at least an index scan, at most an index scan and a sort using that function.
January 29, 2008 at 7:44 am
thanks again todd. i'm somewhat skeptical about what naysayers that never use a feature say about a feature.
them: "it's awful, i never use it for large datasets."
me: "then how do you know it's awful?"
i'll check out the referenced book and decide if we can make use of it.
January 29, 2008 at 11:09 am
Thanks much. This worked great.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply