Take the 2-minute tour ×
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. It's 100% free, no registration required.

Given

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.

Edit:

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

Please vote.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

It seems like you want something like

select *
  from (select person_id,
               rating_date,
               rating,
               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> /

 PERSON_ID RATING_DAT RATING
---------- ---------- --------
         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
(
    SELECT
        PersonID, Ratingdate, rating, 
            ROW_NUMBER() OVER (
                PARTITION BY PersonID
                ORDER BY RatingDate) AS RowNum
    FROM 
        rating
)
SELECT
    R.PersonID, R.Ratingdate, R.rating
FROM
    RatingRowNum R
LEFT OUTER JOIN
    RatingRowNum P 
ON
    R.PersonID = P.PersonID AND
    R.RowNum = P.RowNum+1
WHERE
  R.rating <> P.Rating OR
  P.PersonID IS NULL
ORDER BY
    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

 
discard

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.