This discussion is archived
13 Replies Latest reply: Nov 29, 2012 2:14 AM by 899401 RSS

required last 6 rows only

899401 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    thanks

Legend

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