Want to get max top two rows based on acct_unit

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Want to get max top two rows based on acct_unit

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-10-30 : 05:06:55

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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-30 : 05:21:27

[code]
;with cte
as (select acct_unit, STATUS_DATE, STATUS_FLAG, SUR_KEY,
row_number() over(partition by acct_unit order by STATUS_DATE DESC) rn
FROM @sample
)
SELECT acct_unit, STATUS_DATE, STATUS_FLAG, SUR_KEY
FROM cte
WHERE rn <=2
[/code]

--
Chandu

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-10-30 : 05:44:44

Thanks chandu, can i replace ;cte with my table name. it has all same fields.

Tablename is: TPSI.
it has same fields :select acct_unit, STATUS_DATE, STATUS_FLAG, SUR_KEY from TPSI

Thanks a lot for the helpful info.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-30 : 06:26:00

You need to replace @sample with your table name (TSPI)


;with cte 
as (select acct_unit, STATUS_DATE, STATUS_FLAG, SUR_KEY,
	row_number() over(partition by acct_unit order by STATUS_DATE DESC) rn 
	FROM TSPI
   ) 
SELECT acct_unit, STATUS_DATE, STATUS_FLAG, SUR_KEY
FROM cte
WHERE rn <=2

--
Chandu

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-10-30 : 06:36:04

Hello chandhu, I am using an ETL tool, within sql object i would like to place this code, it automatically will build the table design, based on query.

When i use ;cte it is not recognizing...

Is there a way can i use straight with select column names and then rownumber() over partition.

Thanks for the kind help.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-30 : 07:09:19

Hi, am not aware of ETL tool.....

I modified query by using subquery


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

Let us know further
--
Chandu

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-10-30 : 07:22:55

Thank you very much chandu, it worked great....

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-30 : 07:44:45