SQL Server Performance Forum – Threads Archive

Return selected row#

Hello all, I have been toiling over an issue i have had for a while now and hope that maybe i can get an answer on a few things that would make my life easier. 1) is there a way to call on the row number returned? example. my query brings back 10 records total (top 10) can i say give me return #5 and only that row from my query? if it is not possible to use returned row number as an argument 2) I have been joining my top 10 return to a table that is just a single field that numbers up to 20. What my hope is is that say my top 10 only brings back 3 rows i was hopeing that i would end up from the join with row 1, 2, 3 would be numbered and have data and the rest of the rows would be numbered and have nulls as data. Unfortunately because i have no field to actually JOIN them on that im bringing back every combination of the 2 tables. Can i not just append a number on the return of the top 10? seems simple but it is not. here is some code:
select StatsDataBase.dbo.Sequence.ID , A.*
FROM StatsDataBase.dbo.Sequence, (SELECT top 10 NewInstruments.dbo.VSDQStats.ClientID, NewInstruments.dbo.VSDQStats.Date, NewInstruments.dbo.VSDQStats.TotalDifficulties,
NewInstruments.dbo.VSDQStats.EmotionalSymptomsScale, NewInstruments.dbo.VSDQStats.ConductProblemsScale, NewInstruments.dbo.VSDQStats.HyperActivityScale,
NewInstruments.dbo.VSDQStats.PeerProblemsScale, NewInstruments.dbo.VSDQStats.ProsocialScale, NewInstruments.dbo.VSDQStats.ImpScore FROM NewInstruments.dbo.vsdqstats
WHERE NewInstruments.dbo.VSDQStats.ClientID = ’32AEA2CB-74D7-414F-A218-8371BD66D2A4′
order by NewInstruments.dbo.VSDQStats.date) as A i have tried many different combinations of this code with no Love.

Row Number is not possible with SQL Server 2000. but this feature is avaialbe in SQL Server 2005
You have a workround
http://www.sqlservercentral.com/columnists/dasanka/findminmaxvaluesinaset.asp —————————————-

That is the most messed up solution i have ever seen, but it worked!!! I dont understand how it works or why it does but wow! I am overjoyed! Anyone who has this issue where they need to number returned rows so that they can bring an exact row back should check this out. [<img src=’/community/emoticons/emotion-3.gif’ alt=’ :eek:

‘ />)] thank you soo much dineshasanka

There are quite a few options available. Dinesh already showed you some. Here are some more:http://www.sql-server-performance.com/q&a124.asp —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Why do you want to rely on ROW Number? Madhivanan Failing to plan is Planning to fail

Thank you for the link FrankKalis reading all of the solutions the one i currently have is the only one that will work for me. To Madhivanan:
The reason the solutions with the TOP function, as the query’s backbone, will not work for me is that if i do a top 10 and i want row 10 but only 5 rows come back I dont get a null(which i want) i get row 5. Here is what i am doing: each record in the table i am querying is a test that has been done by a client. Clients do multiple tests. So 1 client could have done 10 tests for example. My view wants to pull back the scores of either the first 10 or last 10 tests and have one record to show it.
eg: clientname Score1 Score2 Score3 Score4 …etc
frank 98 67 74 45 …
Barney 45 56 null null … So if the client didnt do 10 tests i want a null returned not the last score of the last test that was done.
Its a bit of a strange layout but i do what is requested of me. Thank you all

Do a search on "crosstab query".

Now on a new front my function works great. However i am having some difficulty with my view that calls the function. My view works fine when i enter hard coded values into the functions but when i add the table with the clients in I get a syntax error. Here is my View and function code:
The error i get is "line 3: incorrect syntax near ‘.’."
–View
SELECT newinstruments.dbo.vsdqstats.ClientID, A.*, B.*, C.*, D.*
from newinstruments.dbo.vsdqstats LEFT OUTER JOIN
dbo.FSDQScoreGetTable(newinstruments.dbo.vsdqstats.ClientID, 1) as A on newinstruments.dbo.vsdqstats.ClientID = A.ClientID left outer join
dbo.FSDQScoreGetTable(newinstruments.dbo.vsdqstats.ClientID, 2) as B on newinstruments.dbo.vsdqstats.ClientID = B.ClientID LEFT OUTER JOIN
dbo.FSDQScoreGetTable(newinstruments.dbo.vsdqstats.ClientID, 3) as C on newinstruments.dbo.vsdqstats.ClientID = C.ClientID LEFT OUTER JOIN
dbo.FSDQScoreGetTable(newinstruments.dbo.vsdqstats.ClientID, 4) as D on newinstruments.dbo.vsdqstats.ClientID = D.ClientID –Function
ALTER FUNCTION dbo.FSDQScoreGetTable (@ClientID as uniqueidentifier, @SurveyNumber as int)
RETURNS @ScoreTable TABLE
(
ClientID uniqueidentifier,
Date datetime,
TotalDifficulties int,
EmotionalScale int,
ConductScale int,
HyperScale int,
PeerScale int,
ProsocialScale int,
ImpScore int
)
AS
BEGIN
INSERT @ScoreTable select S3.ClientID, S3.Date, S3.TotalDifficulties, S3.EmotionalSymptomsScale, S3.ConductProblemsScale, S3.HyperActivityScale,
S3.PeerProblemsScale, S3.ProsocialScale, S3.ImpScore from (
select count(*) as Rank, A.*
FROM (SELECT top 20 NewInstruments.dbo.VSDQStats.ClientID, NewInstruments.dbo.VSDQStats.Date, NewInstruments.dbo.VSDQStats.TotalDifficulties,
NewInstruments.dbo.VSDQStats.EmotionalSymptomsScale, NewInstruments.dbo.VSDQStats.ConductProblemsScale, NewInstruments.dbo.VSDQStats.HyperActivityScale,
NewInstruments.dbo.VSDQStats.PeerProblemsScale, NewInstruments.dbo.VSDQStats.ProsocialScale, NewInstruments.dbo.VSDQStats.ImpScore
FROM NewInstruments.dbo.vsdqstats
WHERE NewInstruments.dbo.VSDQStats.ClientID = @ClientID
order by NewInstruments.dbo.VSDQStats.date) as A ,
(SELECT top 20 NewInstruments.dbo.VSDQStats.ClientID, NewInstruments.dbo.VSDQStats.Date, NewInstruments.dbo.VSDQStats.TotalDifficulties,
NewInstruments.dbo.VSDQStats.EmotionalSymptomsScale, NewInstruments.dbo.VSDQStats.ConductProblemsScale, NewInstruments.dbo.VSDQStats.HyperActivityScale,
NewInstruments.dbo.VSDQStats.PeerProblemsScale, NewInstruments.dbo.VSDQStats.ProsocialScale, NewInstruments.dbo.VSDQStats.ImpScore
FROM NewInstruments.dbo.vsdqstats
WHERE NewInstruments.dbo.VSDQStats.ClientID = @ClientID
order by NewInstruments.dbo.VSDQStats.date) as B
where A.date >= B.Date
group by A.ClientID, A.Date, A.TotalDifficulties, A.EmotionalSymptomsScale, A.ConductProblemsScale, A.HyperActivityScale, A.PeerProblemsScale, A.ProsocialScale, A.ImpScore
) as S3
where S3.Rank = @SurveyNumber RETURN
END Thanks for all your help guys!

]]>