Select first five rows every for each hour.

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 first five rows every for each hour.

Author

Topic

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-10-02 : 13:46:54

I am trying to write a query that selects the first five survey response for each hour. I would like to Order it by day and hour. Below is my query:

SELECT Q1, SurveyDate, DATEPART(hh, SurveyDate)
AS 'Hour'
FROM hrc_survey_results_2
WHERE (businessline = 4)
ORDER BY SurveyDate, 'Hour'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-02 : 14:56:56

[code]
SELECT Q1,[Hour]
FROM
(
SELECT Q1,ROW_NUMBER() OVER (PARTITION BY DATEPART(hh, SurveyDate) ORDER BY SurveyDate) AS Seq,
DATEPART(hh, SurveyDate) AS 'Hour'
FROM hrc_survey_results_2
WHERE (businessline = 4)
)t
WHERE Seq<=5
ORDER BY DATEADD(dd,DATEDIFF(dd,0,SurveyDate),0), [Hour],Seq
[/code]

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

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-10-02 : 15:05:01

@visakh16,

Irecieved this error code when I ran it. I am using SSRS 2008

The OVER SQL construct or statement is not supported.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-02 : 15:34:12

quote:
Originally posted by sergeant_time

@visakh16,

Irecieved this error code when I ran it. I am using SSRS 2008

The OVER SQL construct or statement is not supported.

where are you using this? Use it in procedure created from SSMS and call it from SSRS. So far as database is above 90 compatibility level it will work fine
Dont use it from query editor within SSRS

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources