flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2013-08-29 : 19:36:42
|
Hey there, try this:declare @table table (id int, rating int)insert into @tablevalues (1, 5),(2, 5),(3, 5),(4, 5),(5, 6),(6, 6),(7, 6),(8, 6),(9, 7),(10, 7),(11, 8),(12, 8),(13, 8),(14, 8),(15, 8),(16, 8),(17, 8),(18, 9),(19, 9),(20, 9)select max(id) as Id, ratingfrom @Tablegroup by ratingId rating----------- -----------4 58 610 717 820 9(5 row(s) affected) EDIT: Actually...I'm lost on the logic. For the first set of ratings (5), you want the highest id...thereafter, you want the lowest id per set of ratings. Is that a typo? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-30 : 00:06:53
|
Assuming id is continuos you can use thisSELECT t1.id,t1.ratingFROM Table t1INNER JOIN Table t2ON t2.id = t1.id -1WHERE t2.rating <> t1.rating if id have gaps use like;WITH cteAS(SELECT ROW_NUMBER() OVER (ORDER BY Id) AS Seq,*FROM Table)SELECT t1.id,t1.ratingFROM CTE t1INNER JOIN CTE t2ON t2.id = t1.id -1WHERE t2.rating <> t1.rating I also hope that 5 was a typo and it should be 8 instead------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|