Select query get top two rows based on status date

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)

Select query get top two rows based on status date

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-10-29 : 20:48:55

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. I have several acct_units available within table.

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.

gamerongvangtp
Starting Member

1 Post

Posted - 2012-10-29 : 21:26:37

Hi, you can use SELECT with option TOP 1 WITH TIES

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 09:23:23

[code]
SELECT ACTIVE_STATUS, ACCT_UNIT, DESCRIPTION, DIRECTOR, DIRECTOR2, STATUS_DATE, OBJ_ID, STATUS_FLAG, SUR_KEY
FROM
(
SELECT ACTIVE_STATUS, ACCT_UNIT, DESCRIPTION, DIRECTOR, DIRECTOR2, STATUS_DATE, OBJ_ID, STATUS_FLAG, SUR_KEY,
ROW_NUMBER() OVER (PARTITION BY ACCT_UNIT ORDER BY STATUS_DATE DESC) AS Seq
FROM STSI
)t
WHERE Seq<=2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources