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
 General SQL Server Forums
 New to SQL Server Programming
 Integers which are greater than ones before them

Author  Topic 

SergioM
Posting Yak Master

170 Posts

Posted - 2013-08-29 : 18:25:22
I have a table which measures the changes in a feedback rating, measured by an integer. Most of my records are the same. Only the primary key & the timestamp change. How do I query just the changes?

Example dataset:
id	rating
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

There are 20 rows & 5 changes. The query I want will result in just those that are different from the ones before them:
id	rating
4 5
5 6
9 7
11 8
18 9


-Sergio
I use Microsoft SQL 2008

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 @table
values
(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, rating
from @Table
group by rating


Id rating
----------- -----------
4 5
8 6
10 7
17 8
20 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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-30 : 00:06:53
Assuming id is continuos you can use this

SELECT t1.id,t1.rating
FROM Table t1
INNER JOIN Table t2
ON t2.id = t1.id -1
WHERE t2.rating <> t1.rating


if id have gaps use like

;WITH cte
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS Seq,*
FROM Table
)
SELECT t1.id,t1.rating
FROM CTE t1
INNER JOIN CTE t2
ON t2.id = t1.id -1
WHERE t2.rating <> t1.rating

I also hope that 5 was a typo and it should be 8 instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -