9 Replies Latest reply: Sep 28, 2013 4:44 PM by dariyoosh RSS

    sql rewrite help

    sk12345678

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          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