| Query Help [message #21713] |
Fri, 23 August 2002 23:18  |
Cleophus Pereira
Messages: 1
Registered: August 2002
|
Junior Member |
|
|
Hi,
I have a query it has following records in a table.
sn , accountno accountname
1 , 111111 AAAAA
2 , 111111 AAAAA
3 , 111111 AAAAA
1 , 222222 BBBBB
2 , 222222 BBBBB
3 , 222222 BBBBB
4 , 222222 BBBBB
I want to write a query which will extract the last two record for accountno.
Ans should be :-
2 , 111111 AAAAA
3 , 111111 AAAAA
3 , 222222 BBBBB
4 , 222222 BBBBB
Can anybody tell how it can be done.
Thanx,
Regards,
Cleophus Pereira
|
|
|
|
| Re: Query Help [message #21721 is a reply to message #21713] |
Sun, 25 August 2002 23:40  |
B
Messages: 327
Registered: August 1999
|
Senior Member |
|
|
please try out this one..
SELECT no,account_no,account_name FROM TEMP_B,(SELECT account_no c1,account_name c2,b1-2 c3 FROM TEMP_B ,
(SELECT account_no a1,COUNT(1) b1 FROM TEMP_B
GROUP BY account_no)a
WHERE account_no=a1
GROUP BY account_no,account_name,b1) c
WHERE account_no=c1 AND no > c3
|
|
|
|
| Re: Query Help [message #21730 is a reply to message #21713] |
Mon, 26 August 2002 12:08 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
It is easy.
Just run the following SELECT exactly as it is, with your table name replacing your_table in the query.
SELECT SN, ACCOUNTNO, ACCOUNTNAME FROM (
SELECT SN, ACCOUNTNO, ACCOUNTNAME, DENSE_RANK() OVER (PARTITION BY ACCOUNTNO ORDER BY SN DESC) RN FROM your_table)
WHERE RN < 3 ORDER BY SN;
The following is my test example I created on my system. Have a look.
SQL> select * from tmp_table;
SNO ACCOUNTNO ACCOUNTNAME
--------- --------- --------------------
1 1111 AAAA
2 1111 AAAA
3 1111 AAAA
1 2222 BBBB
2 2222 BBBB
3 2222 BBBB
4 2222 BBBB
7 rows selected.
SQL> ed
Wrote file afiedt.buf
1 SELECT SNO, ACCOUNTNO, ACCOUNTNAME FROM (
2 SELECT SNO, ACCOUNTNO, ACCOUNTNAME, DENSE_RANK() OVER (PARTITION BY ACCOUNTNO ORDER BY SNO desc
3* WHERE RN < 3 order by sno
SQL> /
SNO ACCOUNTNO ACCOUNTNAME
--------- --------- --------------------
2 1111 AAAA
3 1111 AAAA
3 2222 BBBB
4 2222 BBBB
SQL>
Let me know, if you still need any help.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
