select nth row of a concatenated query

  • Hi dudes...

    i wanna select Nth row of a concatenated column... i wrote this, but it gives me error...

    "SELECT TOP 1 test FROM (SELECT TOP 1 (name+cast(id as char)+xtype) as test FROM master..sysobjects ORDER BY name ASC) sq ORDER BY name DESC "

    any one can help me in this topic?

    Thanks...

  • sh3llm4n (6/27/2012)


    Hi dudes...

    i wanna select Nth row of a concatenated column... i wrote this, but it gives me error...

    "SELECT TOP 1 test FROM (SELECT TOP 1 (name+cast(id as char)+xtype) as test FROM master..sysobjects ORDER BY name ASC) sq ORDER BY name DESC "

    any one can help me in this topic?

    Thanks...

    [name] is not a column of derived table sq. Try this:

    SELECT TOP 1 test

    FROM (

    SELECT TOP 1 (name+cast(id as char)+xtype) as test

    FROM master..sysobjects ORDER BY name ASC

    ) sq

    ORDER BY test DESC

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • also, to get the nth value, i'd add row number to the inner query :

    declare @n int

    SET @n = 4

    SELECT sq.test

    FROM (

    SELECT row_number() over (order by name) As RW,

    (name+cast(id as char)+xtype) as test

    FROM master..sysobjects

    ) sq

    WHERE sq.rw = @n --the 4th value

    ORDER BY sq.test DESC

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • WITH A as

    (SELECT (name+cast(id as char)+xtype) test, row_number() over(order by name asc) rn

    FROM master..sysobjects)

    SELECT * FROM a where rn = 3 --choose whatever row number you want

    -Jake

  • Thanks alot dudes...

    πŸ˜›

    It works as good as past...

  • sh3llm4n (6/30/2012)


    Thanks alot dudes...

    πŸ˜›

    It works as good as past...

    Heh... "dudes". Not a compliment where I come from. πŸ˜‰

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (7/1/2012)


    sh3llm4n (6/30/2012)


    Thanks alot dudes...

    πŸ˜›

    It works as good as past...

    Heh... "dudes". Not a compliment where I come from. πŸ˜‰

    a colloquial meaning, maybe?

    no offence where where I come from...though not the way I would prefer to be addressed...however, saying that, I can confirm that I have been addressed in many ways...several of which I would deem to be far worse than 'dude'.......:w00t:

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply