5 most recent rows with certain value for one

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)

5 most recent rows with certain value for one

Author

Topic

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-11-11 : 06:50:39

Hi,

I need to check if a score of 70 exists at least once in the 5 most recent rows of a table :

I am unsure how I can check for a score of 70 whilst still selecting the top 5 rows for comparison :

IF EXISTS(
SELECT TOP 5 ScoreID,Score FROM Scores ORDER BY Date
)
THEN print 'one score less than 70'

Thank you for any tips..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 06:54:33

below will give you count of 70 scores within recent five rows


SELECT COUNT(CASE WHEN Score =70 THEN 1 ELSE NULL END) AS 70Count
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY date DESC) AS Rn,*
FROM table
)t
WHERE Rn<=5

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources