Post reply

SQL Nested Query ???

  • February 16, 2012 at 8:14 am

    #391575

    Hello,

    I need help structuring a query to retrieve 4 records from 1 table.

    key Col1 Col2 Col3

    =================

    1 0 GRa BL

    2 0 GRb BL

    3 1 GRc BL

    4 0 GRd BL

    5 3 GRe BL

    6 0 GRf BL

    7 5 GRg BL

    8 0 GRh BL

    Assuming my user has selected the record with key = 7, I need to use the value in Col1 (5)

    as the key to the next record, Which has a value in Col1 (3), To the next record ...

    So, my results would be:

    key Col1 Col2 Col3

    =================

    7 5 GRg BL

    5 3 GRe BL

    3 1 GRc BL

    1 0 GRa BL

    Can a single query be constructed for this?

    Thanks,

    TP

  • February 16, 2012 at 8:26 am

    #1447929

    Classic case for recursive CTE.

    Do you want complete answer, or prefer to try googling it out and writing code yourself?

    just a hint:

    http://qa.sqlservercentral.com/articles/Development/recursivequeriesinsqlserver2005/1760/

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • February 16, 2012 at 8:28 am

    #1447931

    kinda looks like a homework question to me. 🙂

    The probability of survival is inversely proportional to the angle of arrival.

  • February 16, 2012 at 12:18 pm

    #1448084

    TP,

    FYI: if you want better answers on the forum, posting only sample data and expected results does not always help us help you. We need DDL code (CREATE TABLE statements with INSERTS for the sample data) and a brief explanation of the columns meanings so we understand how the sample data is supposed to integrate. It can be generic to protect your company's proprietary info, but it still needs to be supplied.

    Here are a few links to guide you:

    How to post data and code to get a better answer[/url]

    Need an answer? No, you need a question.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • February 16, 2012 at 12:23 pm

    #1448090

    I feel like being Flip today. The answer to the original posters question is: Yes, a query can be written to do that task. :crazy:

  • February 16, 2012 at 2:18 pm

    #1448157

    Very helpful, thank you.

    I constructed what I needed with your guidance.

  • February 16, 2012 at 2:20 pm

    #1448159

    No, not homework... I'm new to SQL.

  • February 16, 2012 at 2:22 pm

    #1448160

    Thanks, I'll keep your suggestion in mind next time...

  • February 16, 2012 at 4:00 pm

    #1448214

    tp 25072 (2/16/2012)

    Very helpful, thank you.

    I constructed what I needed with your guidance.

    It's traditional to post your final code so that someone else may benefit. Of course, nothing proprietary, please.

    Shifting gears, how many rows do you have in your real table and how often does that real table receive an update? I ask because there are methods for structuring such hierarchies in a fashion that will blow the doors off of the now traditional Recursive CTE.

    --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)
    Intro to Tally Tables and Functions

Viewing 9 posts - 1 through 8 (of 8 total)

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