This discussion is archived
9 Replies Latest reply: Sep 28, 2013 2:44 PM by dariyoosh RSS

sql rewrite help

sk12345678 Newbie
Currently Being Moderated

I have a select statemnet like below.. it currently returns 3 rows. I want the the last value from this query below. so i want only "4" returned from this query. How to achieve it ? Thanks for the help.

 

SQL

 

SELECT  col6

FROM  TableA

WHERE  col1=12

AND    col2='B'

AND    col3='001'

AND    col4='455'

AND    col5='666'

ORDER BY col1, col2, col3,col5, col4 desc

 

curent output

1

5

4

 

expected output

 

4

  • 1. Re: sql rewrite help
    Etbin Guru
    Currently Being Moderated

    Maybe NOT TESTED!

     

    select col6

      from (select col6

              from (select col6,rownum rn

                      from (SELECT col6

                              FROM TableA

                             WHERE col1 = 12

                               AND col2 = 'B'

                               AND col3 = '001'

                               AND col4 = '455'

                               AND col5 = '666'

                             ORDER BY col1,col2,col3,col5,col4 desc

                           )

                   )

             order by rn desc

            )

    where rownum = 1

     

    Regards

     

    Etbin

     

    Message was edited by: Etbin Another subquery level added See Dariyoosh's post below for the original version

  • 2. Re: sql rewrite help
    dariyoosh Journeyer
    Currently Being Moderated

    Hi,

     

    - You define the term "last" based on what exactly?

    - What is the primary key of the table (if any)?

    - What if several rows have the same value on col6? for example two rows have 4 for col6, which one do you take and based on what rule?

     

    Regards,

    Dariyoosh

  • 3. Re: sql rewrite help
    Anar Godjaev Expert
    Currently Being Moderated

    HI,

     

    SELECT  col6

    FROM  TableA

    WHERE  col1=12

    AND    col2='B'

    AND    col3='001'

    AND    col4='455'

    AND    col5='666'

    AND col6='4'

    ORDER BY col1, col2, col3,col5, col4 desc

     

    equal=4

    got it.....

  • 4. Re: sql rewrite help
    dariyoosh Journeyer
    Currently Being Moderated

    Etbin wrote:

     

    Maybe NOT TESTED!

     

    select col6

      from (select col6,rownum rn

              from (SELECT col6

                      FROM TableA

                     WHERE col1 = 12

                       AND col2 = 'B'

                       AND col3 = '001'

                       AND col4 = '455'

                       AND col5 = '666'

                     ORDER BY col1,col2,col3,col5,col4 desc

                   )

             order by rn desc

           )

    where rownum = 1

     

    Regards

     

    Etbin

    Hi,

     

    Please correct me if I'm wrong, (I didn't test your solution ), but I think there could be a problem in your query which has the following form:

     

    SELECT col6       -

    FROM

    (

         SELECT col6, rownum rn     -- 1st subquery

         FROM

         (

               SELECT col6          -- 2nd subquery

               FROM     . . .

               ORDER BY . . .

         )

         ORDER BY rn DESC

    )

    WHERE rownum = 1

     

    The global query is a top-n-reporting so the order defined in "1st subquery" is conserved within the global query thanks to WHERE rownum = 1, however the order defined by the ORDER BY clause of the "2nd subquery" is not necessarily relevant inside "1st subquery" as there is no top-n structure at this level (inside 1st subquery). In otherwords rownum will not enumerate rows based on the order in the 2nd subquery that the OP defined, that is ORDER BY col1,col2,col3,col5,col4 desc,

     

    Regards,

    Dariyoosh

  • 5. Re: sql rewrite help
    Etbin Guru
    Currently Being Moderated

    Thank you for your remark. I think you're right.

    I might have just been lucky reversing only few rows to test it and substituting OP's query for my data,

    I updated my post pointing to yours to preserve the original without wasting too much forum space.

     

    Regards

     

    Etbin

  • 6. Re: sql rewrite help
    dariyoosh Journeyer
    Currently Being Moderated

    Assuming that there are no duplicates, I think this can do the job (not tested )

     

    1. SELECT t2.col6
    2. FROM
    3. (
    4.     SELECT t1.col6, t1.rn1 rn2
    5.     FROM
    6.     (
    7.         SELECT  col6,
    8.         row_number()
    9.             OVER (ORDER BY col1, col2, col3,col5, col4 desc) rn1
    10.         FROM    TableA
    11.         WHERE   col1=12     AND
    12.                 col2='B'    AND
    13.                 col3='001'  AND
    14.                 col4='455'  AND
    15.                 col5='666'
    16.     ) t1
    17.     ORDER BY t1.rn1 DESC
    18. ) t2
    19. WHERE t2.rn2 = 1;

     

    Regards,

    Dariyoosh

  • 7. Re: sql rewrite help
    Etbin Guru
    Currently Being Moderated

    That was my first thought, but then the OP's so i want only "4" returned from this query made me think the query must not be modified (seems as a non native speaker I start to complicate things much too often)

    Out of curiosity: dealing with a single column and having to return a single row why should the presence of duplicates be relevant?


    Regards


    Etbin

  • 8. Re: sql rewrite help
    dariyoosh Journeyer
    Currently Being Moderated

    Etbin wrote:

    (seems as a non native speaker I start to complicate things much too often)

    Well, you're not alone, I'm also a non native speaker

    Etbin wrote:

     

    Out of curiosity: dealing with a single column and having to return a single row why should the presence of duplicates be relevant?

    Well, he didn't provide any table statement (I mean CREATE TABLE with constraint definition) but only a set of unique values (1, 5, 4), therefore I thought that the uniqueness might be relevant (just a guess, given the fact that he selects only a single column). If there are duplicates, well, the query still works and enumerates each row despite those duplicates but then for me it has no meaning to say that I took the last row just because it had the biggest number assigned by row_number( ) (imagine that the ORDER by clause in the inner subquery ORDER BY col1, col2, col3,col5, col4 desc returns three successive 4 as the last three rows, which one you take and why?) I mean, there should be some more concrete logic/rule for selecting rows.

     

    Of course, just my personal point of view.

     

    Regards,

    Dariyoosh

  • 9. Re: sql rewrite help
    Etbin Guru
    Currently Being Moderated

    imagine that the ORDER by clause in the inner subquery ORDER BY col1, col2, col3,col5, col4 desc returns three successive 4 as the last three rows, which one you take and why?

    trying to be more practical this time:

    • The OP said: I want the the last value from this query
    • The OP used the order by clause (there is an ordered output)
    • So I'd return the last 4 (though I don't know how to to prove it's indeed the last 4 )

     

    Regards

     

    Etbin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points