SQL Server Performance

[Resolved] selecting only row 2 from a select

Discussion in 'T-SQL Performance Tuning for Developers' started by Phthisis, Aug 25, 2006.

  1. Phthisis New Member

    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
  2. chiragkhabaria New Member

    Somthing like this

    Select Top 2 ClientId,Date from vsdqstats order by date.

    Chirag
  3. khtan New Member

    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
  4. Phthisis New Member

    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
  5. kpayne New Member

    quote:Originally posted by Phthisis

    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
  6. Phthisis New Member

    quote:
    "user-defined functions, table data type" and click on "User-Defined Functions That Return a Table" in BOL.

    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?
  7. Adriaan New Member

    It is! Well spotted.
  8. FrankKalis Moderator

    <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>
  9. Phthisis New Member

    Hello Kpayne, thank you for all your help so far.

    1)I have a question about your #2 response:

    quote:
    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.

    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
  10. Phthisis New Member

    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
  11. boppitywop New Member

    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
  12. Phthisis New Member

    quote:Originally posted by boppitywop

    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
  13. Adriaan New Member

    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 ......)

Share This Page