Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top


create table rating (
    ID       int identity(1,1) primary key,
    PersonID int,
    Ratingdate date,
    rating   varchar(2)

insert into rating values ( 1, '2010-08-04' , 'A3');
insert into rating values ( 1, '2010-08-14' , 'A1');

insert into rating values ( 2, '2010-08-04' , 'G2');
insert into rating values ( 2, '2010-08-14' , 'G1');

insert into rating values ( 3, '2010-08-04' , 'G1');
insert into rating values ( 3, '2010-08-10' , 'G1');
insert into rating values ( 3, '2010-08-14' , 'G3');
insert into rating values ( 2, '2010-08-20' , 'G4');

I want a query, which returns all rows, where the rating changes for a given person. For example here I want to exclude the row ( 3, '2010-08-10' , 'G1') because the rating didn't change since ( 3, '2010-08-04' , 'G1').

I know a solution using a cursor, but I wonder, if it could be done without cursors. I tried a bit with analytical functions, but didn't found a solution.

Besides the solution, please improve the terms with which I describe the problem. I guess it is some standard task.


Here is the link to connect asking to add the lag and lead functions to SQL Server.

Please vote.

share|improve this question
up vote 4 down vote accepted

It seems like you want something like

select *
  from (select person_id,
               lag(rating) over (partition by person_id
                                     order by rating_date) prior_rating
         from rating)
 where rating != prior_rating
    or prior_rating is null
 order by person_id, rating_date

which yields the following

SQL> ed
Wrote file afiedt.buf

  1  select person_id,
  2         rating_date,
  3         rating
  4    from (select person_id,
  5                 rating_date,
  6                 rating,
  7                 lag(rating) over (partition by person_id
  8                                       order by rating_date) prior_rating
  9           from rating)
 10   where rating != prior_rating
 11      or prior_rating is null
 12*  order by person_id, rating_date
SQL> /

---------- ---------- --------
         1 2010-08-04 A3
         1 2010-08-14 A1
         2 2010-08-04 G2
         2 2010-08-14 G1
         2 2010-08-20 G4
         3 2010-08-04 G1
         3 2010-08-14 G3

7 rows selected.
share|improve this answer
Thanks, for Oracle that is fine. I hope to get a SQL Server version too. – bernd_k Jun 21 '11 at 16:45

Hopefully this is should work on SQL Server (tested on 2008)

WITH RatingRowNum AS
        PersonID, Ratingdate, rating, 
            ROW_NUMBER() OVER (
                PARTITION BY PersonID
                ORDER BY RatingDate) AS RowNum
    R.PersonID, R.Ratingdate, R.rating
    RatingRowNum R
    RatingRowNum P 
    R.PersonID = P.PersonID AND
    R.RowNum = P.RowNum+1
  R.rating <> P.Rating OR
  P.PersonID IS NULL
    R.PersonID, R.Ratingdate, R.rating 
share|improve this answer
Tested on 2008. I think it works on 2005 too. – bernd_k Jun 22 '11 at 19:37

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.