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 Go to next message
cplusplus1
Messages: 40
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 #569572 is a reply to message #569571] Mon, 29 October 2012 20:46 Go to previous messageGo to next message
BlackSwan
Messages: 23158
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
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 Go to previous messageGo to next message
cplusplus1
Messages: 40
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 #569574 is a reply to message #569573] Mon, 29 October 2012 21:14 Go to previous messageGo to next message
BlackSwan
Messages: 23158
Registered: January 2009
Senior Member
Since we don't have your tables or data, we can not compile, run or test posted code.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
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 Go to previous messageGo to next message
cplusplus1
Messages: 40
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.
Re: Want to get 2 rows based on unit field and sorted by date [message #569600 is a reply to message #569599] Tue, 30 October 2012 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How is this different from your previous topic?
Why don't you feedback and answer in this previous topic?
It is useless to create a new topic, we will merge both.
Save time and answer our questions and post your tries.
I gave you how to do it, what did you try since?

Regards
Michel
Re: Want to get 2 rows based on unit field and sorted by date [message #569601 is a reply to message #569599] Tue, 30 October 2012 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, note that this is an Oracle forum and what you posted is not Oracle compliant.

Regards
Michel
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 Go to previous messageGo to next message
cplusplus1
Messages: 40
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.


Re: I am using following sql query need to use with rank instead of rownum [message #569631 is a reply to message #569629] Tue, 30 October 2012 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you start a THIRD topic on the same subject?
You are boring.
Feedback and answer in the other topic(s).

Regards
Michel
Re: I am using following sql query need to use with rank instead of rownum [message #569632 is a reply to message #569629] Tue, 30 October 2012 08:36 Go to previous message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it seems like row_number is n't going to work with oracle, i may need to use RANK.


It seems you did NOT read the answers I posted you.

Regards
Michel
Previous Topic: How to create a view
Next Topic: How to get multiple column values in a row
Goto Forum:
  


Current Time: Sun Dec 21 15:42:53 CST 2014

Total time taken to generate the page: 0.05504 seconds