Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    3

    Unanswered: ORder by clause in nested query and a rownum

    Hi all,
    I have a situation to handle where in I need to write a query to give me the below reult.
    Please help.

    Select T1.A from T1 where T1.B in (select T2.C from T2 where T2.D in (select T3.E from T3)orderby C desc) and rownum =1;

    My requirement here is to have an orderby clause and on the sorted list I want to do a select and select the first record.

    The restrcition that I know is Order by has to be at the end and I cannot select A from T1 if I am applying orderby on C in this case.

    Any better way to get this done?
    How can I write the query for the desired output.

    TIA
    Vishal.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select junk
    from
    (select junk
    from my_table
    order by junk)
    where rownum = 1;

    figure out your own query.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    May 2009
    Posts
    3
    Thanks Bill, but my question is different and the solution provided does not apply.
    The order by clause is to be applied at 3rd level which it is not permissing me.

    So wanted to knwo if I can do that.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post DDL for tables.
    Post DML for test data.

    Post expected/desired results.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2009
    Posts
    3
    The expected result is:
    I want to have the first record of A in T1 where orderby clause is applied on C in T3

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I want to have the first record of A
    Rows in a table are like balls in a basket.
    Which ball is the first ball?

    Post DDL for tables.
    Post DML for test data.

    Post expected/desired results.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •