Insert max value - SQL Server Forums

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)

Insert max value

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2012-05-15 : 16:06:51

Hi,

I have a table with 8 records by day for each ID_A and ID_Z combination and I need to tell what hour has the highest value for each combination.I want to show Yes in the busyhour to indicate that it is the highest PingValue for that day for that ID_A and ID_Z and show No when that is not the case
-----------------------------------
|ID_A|ID_Z|Hour|PingValue|BusyHour|
|63|24|8:00:00|3|NO
|63|24|9:00:00|4|NO
|63|24|10:00:00|1|NO
|63|24|11:00:00|15|YES
|63|24|12:00:00|12|NO
|63|24|13:00:00|2|NO
|91|44|8:00:00|3|NO
|91|44|9:00:00|4|NO
|91|44|10:00:00|1|NO
|91|44|11:00:00|10|NO
|91|44|12:00:00|12|YES
|91|44|13:00:00||2|NO
----------------------
Thanks!

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-05-15 : 18:46:13

Try something like this:


SELECT T.ID_A, T.ID_Z, T.Hour, T.PingValue,
       CASE WHEN T.row_num = 1 THEN 'Yes' ELSE 'No' END AS BusyHour
  FROM (SELECT T.ID_A, T.ID_Z, T.Hour, T.PingValue,
               ROW_NUMBER() OVER(PARTITION BY T.ID_A, T.ID_Z
                                     ORDER BY T.PingValue DESC) AS row_num
          FROM YourTable AS T) AS T

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

infodemers
Posting Yak Master

183 Posts

Posted - 2012-05-15 : 22:22:49

Hi Malpashaa,

That's exactly what I was looking for.

Thanks a lot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources