SQL Server Performance Forum – Threads Archive

[Resolved] selecting only row 2 from a select

Hello all, what i am looking to do is select a few records but only return what would be row 2 or row 5. Is this possible? Here is my selection code:
select ClientID, Date from vsdqstats order by date this returns 200 ish rows. I want row 2. The reason i want to select in this manner is for stats purposes. Each of the items in the table is a survey done on a client of which there are multiple serveys done on the same client. I want to bring back the second servey done or the third etc. In the end this code will go inside a function that will be called by a view.
the view code will be somewhat like this: select clientname, GetScore(cliendID, SurveyNumber) thanks in advance

---

Somthing like this Select Top 2 ClientId,Date from vsdqstats order by date. Chirag

---

To get Row No 2 select top 1 *
from
(
Select Top 2 ClientId,Date from vsdqstats order by date
) a
order by date desc
KH

---

thank you for your replies guys. I have built my function and here it is: alter FUNCTION dbo.FSDQScoreGet (@ClientID as uniqueidentifier, @SurveyNumber as int)
RETURNS INT–uniqueidentifier, datetime, int, int, int, int, int, int, int AS
BEGIN
RETURN (
SELECT TOP 1 A.TotalDifficulties from (SELECT TOP @SurveyNumber 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 @ClientID = NewInstruments.dbo.VSDQStats.ClientID order by NewInstruments.dbo.VSDQStats.date) as A
order by A.date desc
) END –Code for the view: select NewInstruments.dbo.VSDQStats.ClientID, dbo.FSDQScoreGet(NewInstruments.dbo.VSDQStats.ClientID, 1)
, dbo.FSDQScoreGet(NewInstruments.dbo.VSDQStats.ClientID, 2)
, dbo.FSDQScoreGet(NewInstruments.dbo.VSDQStats.ClientID, 3)
, dbo.FSDQScoreGet(NewInstruments.dbo.VSDQStats.ClientID, 4)
, dbo.FSDQScoreGet(NewInstruments.dbo.VSDQStats.ClientID, 5)
, dbo.FSDQScoreGet(NewInstruments.dbo.VSDQStats.ClientID, 6)
, dbo.FSDQScoreGet(NewInstruments.dbo.VSDQStats.ClientID, 7)
from NewInstruments.dbo.VSDQStats I have three issues i hope someone has the answer to.
1) looking at my commented out code after the ‘Returns’ statement i have 7 fields i would like the function to return. Is this possible? For my tests i just have it returning one of the fields. 2) In the Inner Select clause i have "SELECT TOP @SurveyNumber". This is giving me an error and wants a hardcoded number in there. Is there any way to have this be dynamic? 3) The issue i just noticed is that when there are only, say, 3 surveys ever done on the client and i want the score of the 6th survey the above code will bring back the third surveys score for every request past 3 instead of a null. How would you code it to say that if you cant produce, say 10 records return a null? thank you for your time

---

1. Use a table-valued function instead of a scalar-valued function. See "user-defined functions, table data type" and click on "User-Defined Functions That Return a Table" in BOL. 2. You can’t use a variable with the TOP clause. To use the parameter you will have to create a sequence table-valued function, join your table to it and put mySeq.SequenceNo = @SurveyNumber in the WHERE clause. 3. Answer #2 will take care of this. Keith Payne
Technical Marketing Solutions
www.tms-us.com

---

Hello kpayne i just have a quick noobie question: what and where is BOL? Is it that books online thing in the MSSQL server program list?

---

It is! Well spotted.

---

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />what and where is BOL? Is it that books online thing in the MSSQL server program list?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Hm, on my machine it also opens when I hit the F1 key. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />In difference to other MS help files, the Books Online of SQL Server are _really_ exceptionally good. You’ll find answers to almost any question you might have. The only problem is that it sometimes takes much time to get to the information you need. Nonetheless it’s always the first place to look for answers.<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

---

Hello Kpayne, thank you for all your help so far. 1)I have a question about your #2 response:
I dont know what this is and i couldn’t find anything on it in the Online books using "sequence table-valued function" as a search word. 2)And on a side note i did the change to my function to make it return a table but my view doesn’t accept this function anymore(renamed the function by adding the word ‘table’ to the end). I get this error "Invalid function name ‘dbo.FSDQScoreGetTable’" I have checked the spelling and it is correct. Your help is much appreciated

---

Hello all, I am still looking for an answer to my issue. Here is my code, and i think i may understand what kpayne was refering to in an earlier post.: 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 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 @ClientID = NewInstruments.dbo.VSDQStats.ClientID
order by NewInstruments.dbo.VSDQStats.date) as A
INNER JOIN StatsDataBase.dbo.Sequence
WHERE StatsDataBase.dbo.Sequence.ID = @SurveyNumber <–syntax error order by A.date desc
RETURN
END Sequence is a table i created in the same database that the function exists. It has only one field "ID" and i have roughly 20 records in it containing an integer value starting at 1 and going to 20. Theoretically by joining this table to the Select that brings back the top 20 scores of a client i append a sequence number (which is more or less what i was trying to do in the first place). From that i can bring back row "5" with the where clause and this also solves my problem of needing to return nulls when there aren’t 5 surveys done. The issue i am facing at the moment with the above code is that i am getting a syntax error on my where statement "Incorrect syntax". I cant see where my issue is and i have been toiling over this for a bit. Any help much appreciated

---

I’m wondering can you add a sequence ID to the survey table. So that table would have ClientID SurveySequence
1 1
1 2
2 1
2 2
2 3
2 4
3 1
3 2 Then you just need to select where SurveySequence = @SurveyNumber

---

thats code i would like to avoid developing. I was hoping to base this off of the date the survey was created and order it that way. Going back and changing my initial program would take far too much time. I am very close to getting this to work. Concerning my function i removed the "Inner Join" and replaced it with a comma and i no longer got the syntax error. Now i still need to test the function in my view however my view still is having issues: SELECT NewInstruments.dbo.VSDQStats.ClientID, A.*, B.*
from NewInstruments.dbo.VSDQStats, dbo.FSDQScoreGetTable(NewInstruments.dbo.VSDQStats.ClientID, 1) as A, dbo.FSDQScoreGetTable(NewInstruments.dbo.VSDQStats.ClientID, 2) as B this view code is giving me a synax error. I am unsure as to how I can get the view to work. What the function does is returns the roughly 6 scores from one of the surveys done. One of the parameters the function requires is the client id to look pull all surveys done on that client and the second parameter of the function is to pull the survey number. The surveys get numbered in the function by appending sequence number to the surveys (which are ordered by date).
What the view does or is supposed to to is in one record show me the clients ID and then the first 10 scores of the surveys. since the function returns 6 fiels i am looking at 61 fields in a row. Hope that helps clarify what im trying to accomplish

---

I’d solve the "GROUP BY with TOP 10" problem along these lines: SELECT M.Client, detail.*
FROM detail
INNER JOIN (SELECT DISTINCT Client FROM master) M
ON M.Client = detail.Client
WHERE detail.detailid in
(SELECT TOP 10 t1.detailid FROM detail t1
WHERE t1.Client = detail.Client
ORDER BY ……)

---

]]>