This content has been marked as final.
Show 13 replies

1. Re: required last 6 rows only
Hoek Nov 27, 2012 7:21 PM (in response to 899401)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/issuearchive/2007/07jan/o17asktom093877.html
and: {message:id=9360002}
and: http://tkyte.blogspot.nl/2005/06/howtoaskquestions.html 
2. Re: required last 6 rows only
899401 Nov 27, 2012 7:37 PM (in response to 899401)hI,
I have below mentioend output of my report but i want only data from column D278 and onwards lets say last 6 rows,how will i get that
A B C D E
93374N 02RX T12 283 11
93374N 02RX T12 282 12
93374N 02RX T12 281 24
93374N 02RX T12 280 7
93374N 02RX T12 279 1
93374N 02RX T12 278 1
93374N 02RX T12 253 118
93374N 02RX T12 230 77
93374N 02RX T12 209 85
93374N 02RX T12 190 77
93374N 02RX T12 173 20
thanks
Edited by: 896398 on Nov 27, 2012 11:37 AM 
3. Re: required last 6 rows only
JustinCave Nov 27, 2012 7:55 PM (in response to 899401)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
Depending on how you want to handle ties, you may want to use the dense_rank or the row_number function.SELECT * FROM (SELECT a, b, c, d, e, rank() over (order by d asc) rnk FROM table_name) WHERE rnk <= 6
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
JustinSELECT * FROM (SELECT a, b, c, d, e, rank() over (partition by a order by d asc) rnk FROM table_name) WHERE rnk <= 6

4. Re: required last 6 rows only
899401 Nov 27, 2012 9:59 PM (in response to JustinCave)Hi
I want last six rows from above data i.e.
A B C D F
93374N 02RX T12 278 1
93374N 02RX T12 253 118
93374N 02RX T12 230 77
93374N 02RX T12 209 85
93374N 02RX T12 190 77
93374N 02RX T12 173 20
from above records and in other words last six rows
thanks 
5. Re: required last 6 rows only
JustinCave Nov 27, 2012 10:04 PM (in response to 899401)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 Nov 27, 2012 10:08 PM (in response to 899401)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 Nov 27, 2012 10:32 PM (in response to John Spencer)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 Nov 27, 2012 10:38 PM (in response to 899401)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 Nov 28, 2012 10:06 AM (in response to JustinCave)Hi,
It should start from 278 and then 67 rows additional
A B C D F
93374N 02RX T12 278 1
93374N 02RX T12 253 118
93374N 02RX T12 230 77
93374N 02RX T12 209 85
93374N 02RX T12 190 77
93374N 02RX T12 173 20
thanks 
10. Re: required last 6 rows only
Purvesh K Nov 28, 2012 10:17 AM (in response to 899401)896398 wrote:
It look like you do not have any Business Logic to differentiate the Last 67 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);
Hi,
It should start from 278 and then 67 rows additional
A B C D F
93374N 02RX T12 278 1
93374N 02RX T12 253 118
93374N 02RX T12 230 77
93374N 02RX T12 209 85
93374N 02RX T12 190 77
93374N 02RX T12 173 20
thanks
This may help you get 6 rows, you no certainty of getting a stable output.
You may adapt by taking this example as reference. And please do not expect people to spoonfeed you by writing an exact SQL that you can just Copy and Paste.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;

11. Re: required last 6 rows only
chris227 Nov 30, 2012 7:47 AM (in response to 899401)
Edited by: chris227 on 28.11.2012 04:34with data as ( select '93374N' a, '02RX' b, 'T12' c, '310' d, '60' e from dual union all select '93374N', '02RX', 'T12', '309', '1' from dual union all select '93374N', '02RX', 'T12', '308', '1' from dual union all select '93374N', '02RX', 'T12', '307', '1' from dual union all select '93374N', '02RX', 'T12', '305', '0' from dual union all select '93374N', '02RX', 'T12', '303', '1' from dual union all select '93374N', '02RX', 'T12', '301', '8' from dual union all select '93374N', '02RX', 'T12', '299', '0' from dual union all select '93374N', '02RX', 'T12', '295', '9' from dual union all select '93374N', '02RX', 'T12', '294', '2' from dual union all select '93374N', '02RX', 'T12', '292', '' from dual union all select '93374N', '02RX', 'T12', '290', '1' from dual union all select '93374N', '02RX', 'T12', '288', '0' from dual union all select '93374N', '02RX', 'T12', '286', '0' from dual union all select '93374N', '02RX', 'T12', '284', '0' from dual union all select '93374N', '02RX', 'T12', '283', '11' from dual union all select '93374N', '02RX', 'T12', '282', '12' from dual union all select '93374N', '02RX', 'T12', '281', '24' from dual union all select '93374N', '02RX', 'T12', '280', '7' from dual union all select '93374N', '02RX', 'T12', '279', '1' from dual union all select '93374N', '02RX', 'T12', '278', '1' from dual union all select '93374N', '02RX', 'T12', '253', '118' from dual union all select '93374N', '02RX', 'T12', '230', '77' from dual union all select '93374N', '02RX', 'T12', '209', '85' from dual union all select '93374N', '02RX', 'T12', '190', '77' from dual union all select '93374N', '02RX', 'T12', '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 T12 278 1 93374N 02RX T12 253 118 93374N 02RX T12 230 77 93374N 02RX T12 209 85 93374N 02RX T12 190 77 93374N 02RX T12 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: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 Nov 28, 2012 7:48 PM (in response to 899401)It appears that it's ordered by the D column descending. Soselect 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;
