13 Replies Latest reply: Nov 29, 2012 4:14 AM by 899401 RSS

    required last 6 rows only

    899401
      hI,

      I have below mentioend output of my report but i want only data from hammer-278 and onwards lets say last 6 rows,how will i get that
      A     B      C      D E
      93374N     02RX     T-1-2     310     60
      93374N     02RX     T-1-2      309     1
      93374N     02RX     T-1-2     308     1
      93374N     02RX     T-1-2     307     1
      93374N     02RX     T-1-2 305     0
      93374N     02RX     T-1-2     303     1
      93374N     02RX     T-1-2     301      8
      93374N     02RX     T-1-2     299     0
      93374N     02RX     T-1-2     295     9
      93374N     02RX     T-1-2     294     2
      93374N     02RX     T-1-2     292     
      93374N     02RX     T-1-2     2 90     1
      93374N     02RX     T-1-2     288     0
      93374N     02RX     T-1-2     286     0
      93374N     02RX     T-1-2     284     0
      93374N     02RX     T-1-2 283     11
      93374N     02RX     T-1-2     282     12
      93374N     02RX     T-1-2     281     24
      93374N     02RX     T-1-2     280     7
      93374N     02RX     T-1-2     279     1
      93374N     02RX     T-1-2     *278*     1
      93374N     02RX     T-1-2     253     118
      93374N     02RX     T-1-2     230     77
      93374N     02RX     T-1-2     209     85
      93374N     02RX     T-1-2     190     77
      93374N     02RX     T-1-2     173     20

      thanks

      Edited by: 896398 on Nov 27, 2012 11:30 AM

      Edited by: 896398 on Nov 27, 2012 11:31 AM
        • 1. Re: required last 6 rows only
          Hoek
          Can you please post CREATE TABLE and INSERT INTO statements, or read:

          Please read: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:912210644860
          and: http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html
          and: {message:id=9360002}
          and: http://tkyte.blogspot.nl/2005/06/how-to-ask-questions.html
          • 2. Re: required last 6 rows only
            899401
            hI,

            I have below mentioend output of my report but i want only data from column D-278 and onwards lets say last 6 rows,how will i get that
            A B C D E
            93374N 02RX T-1-2 283 11
            93374N 02RX T-1-2 282 12
            93374N 02RX T-1-2 281 24
            93374N 02RX T-1-2 280 7
            93374N 02RX T-1-2 279 1
            93374N 02RX T-1-2 278 1
            93374N 02RX T-1-2 253 118
            93374N 02RX T-1-2 230 77
            93374N 02RX T-1-2 209 85
            93374N 02RX T-1-2 190 77
            93374N 02RX T-1-2 173 20


            thanks

            Edited by: 896398 on Nov 27, 2012 11:37 AM
            • 3. Re: required last 6 rows only
              JustinCave
              Posting the data that you did is helpful. It is much more helpful, though, to post DDL and DML statements so that we can (easily) create a table on our systems that matches the one you have. That allows us to do things like test our solutions.

              It is also helpful to explain in words exactly what logic the query should use. Since a heap organized table is unordered, there is no such thing as the "first row" or "last row" in a table. My guess is that "last 6 rows" means "sort the data by column D which is defined as a NUMBER and return the 6 rows with the smallest D values". If there can be ties, you'll need to tell us how to handle ties-- if there are two rows tied for the sixth lowest D value, do you want to return both (so the query returns 7 rows)? Do you want to pick one arbitrarily (so the result set is indeterminant)? Do you want to use a different column to break the tie?

              My guess is that you want something like
              SELECT *
                FROM (SELECT a, b, c, d, e, rank() over (order by d asc) rnk
                        FROM table_name)
               WHERE rnk <= 6
              Depending on how you want to handle ties, you may want to use the dense_rank or the row_number function.

              I'm also suspecting that the real table has many different A values and that you really want the 6 rows with the lowest D value for each A value. If that is the case, you'd need to add a PARTITION BY clause to the analytic function
              SELECT *
                FROM (SELECT a, b, c, d, e, rank() over (partition by a order by d asc) rnk
                        FROM table_name)
               WHERE rnk <= 6
              Justin
              • 4. Re: required last 6 rows only
                899401
                Hi

                I want last six rows from above data i.e.
                A B C D F
                93374N 02RX T-1-2 278 1
                93374N 02RX T-1-2 253 118
                93374N 02RX T-1-2 230 77
                93374N 02RX T-1-2 209 85
                93374N 02RX T-1-2 190 77
                93374N 02RX T-1-2 173 20

                from above records and in other words last six rows

                thanks
                • 5. Re: required last 6 rows only
                  JustinCave
                  Well, I think I gave you two different queries that would get the results you're looking for. Are you saying that neither of those implements the logic that you want? If so, what alternative logic are you looking for? I explained exactly what assumptions I was making and what logic I was guessing you were looking for. If my guesses and assumptions are incorrect, please let us know in words exactly what you want the query to do keeping in mind that the terms "first" and "last" are meaningless without something to order the results by.

                  Justin
                  • 6. Re: required last 6 rows only
                    John Spencer
                    Repeating the same thing over and over is not going to get you an answer.

                    There is no such thing as the "last" rows in a table. In order to get the "last" rows form the table, you need to sort them by some column or columns. Justin's example above suggested one possible sort column. If he did not guess correctly, you need to decide for yourself what that column would be and adapt justin's solution for your requirements.

                    John
                    • 7. Re: required last 6 rows only
                      899401
                      Hi,

                      Acutually rank is not satisfying the values becasue last 6 rows starts with 278 values but sometimes it shows 7 or 8 values but common thing is starting with 278
                      rnk <= 6

                      thanks
                      • 8. Re: required last 6 rows only
                        JustinCave
                        Are you saying that with the data you posted, you are getting 7 or 8 rows? Or that you are getting 7 or 8 rows with a different data set that you haven't posted? Are you getting more rows because there are ties? If so, as I said originally, you'll need to tell us how you want to handle ties.

                        Justin
                        • 9. Re: required last 6 rows only
                          899401
                          Hi,

                          It should start from 278 and then 6-7 rows additional

                          A B C D F
                          93374N 02RX T-1-2 278 1
                          93374N 02RX T-1-2 253 118
                          93374N 02RX T-1-2 230 77
                          93374N 02RX T-1-2 209 85
                          93374N 02RX T-1-2 190 77
                          93374N 02RX T-1-2 173 20

                          thanks
                          • 10. Re: required last 6 rows only
                            Purvesh K
                            896398 wrote:
                            Hi,

                            It should start from 278 and then 6-7 rows additional

                            A B C D F
                            93374N 02RX T-1-2 278 1
                            93374N 02RX T-1-2 253 118
                            93374N 02RX T-1-2 230 77
                            93374N 02RX T-1-2 209 85
                            93374N 02RX T-1-2 190 77
                            93374N 02RX T-1-2 173 20

                            thanks
                            It look like you do not have any Business Logic to differentiate the Last 6-7 rows (In fact you do not even know if you need 6 rows or 7 rows, so unsure how are you going to manage the fetching logic);

                            This may help you get 6 rows, you no certainty of getting a stable output.
                            with data as
                            (
                              select 'AABC' col1, 1 col2 from dual union all
                              select 'AABC' col1, 3 col2 from dual union all
                              select 'AABC' col1, 5 col2 from dual union all
                              select 'AABC' col1, 2 col2 from dual union all
                              select 'AABC' col1, 4 col2 from dual union all
                              select 'AABC' col1, 9 col2 from dual union all
                              select 'AABC' col1, 8 col2 from dual union all
                              select 'AABC' col1, 10 col2 from dual union all
                              select 'AABC' col1, 6 col2 from dual
                            ),
                            recs as
                            (
                              select col1, col2, row_number() over(order by 1) rn
                                from data
                               order by rn desc
                            )
                            select col1, col2, rn
                              from recs
                             where rownum <= 6;
                            You may adapt by taking this example as reference. And please do not expect people to spoon-feed you by writing an exact SQL that you can just Copy and Paste.
                            • 11. Re: required last 6 rows only
                              chris227
                              with data as (
                              select '93374N' a, '02RX' b, 'T-1-2' c, '310' d, '60' e from dual union all
                              select '93374N', '02RX', 'T-1-2', '309', '1' from dual union all
                              select '93374N', '02RX', 'T-1-2', '308', '1' from dual union all
                              select '93374N', '02RX', 'T-1-2', '307', '1' from dual union all
                              select '93374N', '02RX', 'T-1-2', '305', '0' from dual union all
                              select '93374N', '02RX', 'T-1-2', '303', '1' from dual union all
                              select '93374N', '02RX', 'T-1-2', '301', '8' from dual union all
                              select '93374N', '02RX', 'T-1-2', '299', '0' from dual union all
                              select '93374N', '02RX', 'T-1-2', '295', '9' from dual union all
                              select '93374N', '02RX', 'T-1-2', '294', '2' from dual union all
                              select '93374N', '02RX', 'T-1-2', '292', '' from dual union all
                              select '93374N', '02RX', 'T-1-2', '290', '1' from dual union all
                              select '93374N', '02RX', 'T-1-2', '288', '0' from dual union all
                              select '93374N', '02RX', 'T-1-2', '286', '0' from dual union all
                              select '93374N', '02RX', 'T-1-2', '284', '0' from dual union all
                              select '93374N', '02RX', 'T-1-2', '283', '11' from dual union all
                              select '93374N', '02RX', 'T-1-2', '282', '12' from dual union all
                              select '93374N', '02RX', 'T-1-2', '281', '24' from dual union all
                              select '93374N', '02RX', 'T-1-2', '280', '7' from dual union all
                              select '93374N', '02RX', 'T-1-2', '279', '1' from dual union all
                              select '93374N', '02RX', 'T-1-2', '278', '1' from dual union all
                              select '93374N', '02RX', 'T-1-2', '253', '118' from dual union all
                              select '93374N', '02RX', 'T-1-2', '230', '77' from dual union all
                              select '93374N', '02RX', 'T-1-2', '209', '85' from dual union all
                              select '93374N', '02RX', 'T-1-2', '190', '77' from dual union all
                              select '93374N', '02RX', 'T-1-2', '173', '20' from dual
                              )
                              
                              select
                              a,b,c,d,e
                              from
                              (select
                              a,b,c,d,e
                              ,row_number() over(order by d) rn
                              from data
                              )
                              where
                              rn < 7
                              order by d desc
                              
                              A     B     C     D     E
                              93374N     02RX     T-1-2     278     1
                              93374N     02RX     T-1-2     253     118
                              93374N     02RX     T-1-2     230     77
                              93374N     02RX     T-1-2     209     85
                              93374N     02RX     T-1-2     190     77
                              93374N     02RX     T-1-2     173     20
                              
                              Or if you want to start for example with 278 and get at most 6 or less rows
                              
                              select
                              a,b,c,d,e
                              from
                              (select
                              a,b,c,d,e
                              ,row_number() over(order by d) rn
                              from data
                              where
                              d <= 278 -- start point
                              )
                              where
                              rn < 7
                              order by d desc
                              Edited by: chris227 on 28.11.2012 04:34

                              Edited by: chris227 on 28.11.2012 04:35

                              Edited by: chris227 on 29.11.2012 23:46
                              d <= 278 -- start point instead of e <=
                              • 12. Re: required last 6 rows only
                                kendenny
                                It appears that it's ordered by the D column descending. So
                                select a,b,c,d,e from
                                (select a,b,c,d,e, row_number() over (order by d) rm
                                    from table1)
                                 where rn <= 6
                                 order by d desc;
                                • 13. Re: required last 6 rows only
                                  899401
                                  thanks