Home » SQL & PL/SQL » SQL & PL/SQL » Select query to get top two rows based on statusdate (merged 3) (oracle11)
| Select query to get top two rows based on statusdate (merged 3) [message #569571] |
Mon, 29 October 2012 19:47  |
 |
cplusplus1
Messages: 30
Registered: October 2012
Location: usa
|
Member |
|
|
I want to get top two rows based on ACCT_UNIT & order by status_date, if there is only one row on acct_unit, get one row.
IF more than two rows available, want to get the top two rows based on status_date.
SELECT ACTIVE_STATUS, ACCT_UNIT, DESCRIPTION, DIRECTOR, DIRECTOR2, STATUS_DATE, OBJ_ID, STATUS_FLAG, SUR_KEY
FROM STSI
Thanks a lot for the helpful info.
|
|
|
|
|
|
| Re: Select query to get top two rows based on statusdate [message #569573 is a reply to message #569572] |
Mon, 29 October 2012 21:10  |
|
cplusplus1
Messages: 30
Registered: October 2012
Location: usa
|
Member |
|
|
I am using this but i am getting almost 500 rows, expecting 28 rows.
SELECT ACTIVE_STATUS, ACCT_UNIT, DESCRIPTION, DIRECTOR, DIRECTOR2, STATUS_DATE, OBJ_ID, STATUS_FLAG, SUR_KEY
(SELECT ROW_NUMBER () OVER (PARTITION BY OBJ_ID ORDER BY STATUS_DATE DESC) as RNUM, ACTIVE_STATUS, ACCT_UNIT, DESCRIPTION, DIRECTOR, DIRECTOR2, STATUS_DATE, OBJ_ID, STATUS_FLAG, SUR_KEY FROM STSI) STSITEST
WHERE STSITEST.RNUM = 1 AND STATUS_FLAG <> 'Deleted Record'
Thanks for the helpful info.
|
|
|
|
|
|
| Re: Select query to get top two rows based on statusdate [message #569583 is a reply to message #569571] |
Tue, 30 October 2012 00:45 |
 |
Michel Cadot
Messages: 54219
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
What happens to the top 2 when there is the 3 times the same top date?
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions and at http://www.orafaq.com/forum/mv/msg/160920/472554/102589/#msg_472554 post.
SQL> select empno, ename, sal,
2 row_number() over (order by sal desc) "ROW_NUMBER",
3 rank() over (order by sal desc) "RANK",
4 dense_rank() over (order by sal desc) "DENSE_RANK"
5 from emp
6 /
EMPNO ENAME SAL ROW_NUMBER RANK DENSE_RANK
---------- ---------- ---------- ---------- ---------- ----------
7839 KING 5000 1 1 1
7902 FORD 3000 2 2 2
7788 SCOTT 3000 3 2 2
7566 JONES 2975 4 4 3
7698 BLAKE 2850 5 5 4
7782 CLARK 2450 6 6 5
7499 ALLEN 1600 7 7 6
7844 TURNER 1500 8 8 7
7934 MILLER 1300 9 9 8
7521 WARD 1250 10 10 9
7654 MARTIN 1250 11 10 9
7876 ADAMS 1100 12 12 10
7900 JAMES 950 13 13 11
7369 SMITH 800 14 14 12
With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
Regards
Michel
[Updated on: Tue, 30 October 2012 00:46] Report message to a moderator |
|
|
|
| Want to get 2 rows based on unit field and sorted by date [message #569599 is a reply to message #569571] |
Tue, 30 October 2012 04:10 |
|
cplusplus1
Messages: 30
Registered: October 2012
Location: usa
|
Member |
|
|
I am using an etl tool, thru that it is not possible to get the required data, looking for a query, i am not really proficient in queries: please kindly need advice on this.
Is it possible, i want to get top two rows if available based on each acct_unit and sort by status_date
If just row available on an acct_unit, want to get that one aswell, if there are more than 2 rows on an acct_unit, want to get just top rows sorted by status_date.
Declare @Sample table (acct_unit varchar(10), STATUS_DATE datetime, STATUS_FLAG varchar(20), SUR_KEY Int)
insert @Sample
select '168','12-Oct-2012', 'Initial', 6 union all
select '173','14-Oct-2012', 'Updated', 9 union all
select '168','16-Oct-2012', 'Updated', 12 union all
select '182','17-Oct-2012', 'Initial', 14 union all
select '168','20-Oct-2012', 'Updated', 19 union all
select '173','20-Oct-2012', 'Updated', 22 union all
select '173','24-Oct-2012', 'Updated', 26
Result should be these 5 rows: 168(2 rows), 173(2 rows) , 182 just one row.
168 20-OCT-2012 'Updated' 19
168 16-OCT-2012 'Updated' 12
173 24-OCT-2012 'Updated' 26
173 20-OCT-2012 'Updated' 22
182 17-OCT-2012 'Initial' 14
Thank you very much for the helpful info.
|
|
|
|
|
|
|
|
| I am using following sql query need to use with rank instead of rownum [message #569629 is a reply to message #569571] |
Tue, 30 October 2012 08:25 |
|
cplusplus1
Messages: 30
Registered: October 2012
Location: usa
|
Member |
|
|
I am using this query, with sql server. Can someone please help use this with oracle.
it seems like row_number is n't going to work with oracle, i may need to use RANK.
SELECT acct_unit, STATUS_DATE, STATUS_FLAG, SUR_KEY
FROM (select acct_unit, STATUS_DATE, STATUS_FLAG, SUR_KEY,
row_number() over(partition by acct_unit order by STATUS_DATE DESC) rn
FROM TSPI
) temp
WHERE rn <=2
Kind regards. Thank you.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu May 23 13:18:31 CDT 2013
Total time taken to generate the page: 0.23514 seconds
|
] 
Members
Search
Help
Register
Login
Home
