Searching for changes in an ordered recordset

  • This is a contrived example that's analagous to something I'm trying to do in the "real world."  Suppose I have a table of users and their favorite colors on every day.  My table might look like this:

    User ID, Date, Favorite Color

    1 9/18/05 Blue

    1 9/19/05 Blue

    1 9/20/05 Red

    1 9/21/05 Blue

    2 9/18/05 Green

    2 9/19/05 Green

    2 9/20/05 Green

    2 9/21/05 Yellow

    Now what I want is a table that removes the redundancies: I only want to keep the records where a user chose a new favorite color, like so:

    1 9/18/05 Blue

    1 9/20/05 Red

    1 9/21/05 Blue

    2 9/18/05 Green

    2 9/21/05 Yellow

    Is it possible to accomplish this with straight SQL, or am I going to need to write some T-SQL to loop through this and make some comparisons?  And if I must use T-SQL, do I need to use a cursor, or is there some other facility for looping through ordered recordsets? 

    Also, sorry for all the double-spacing; can this be turned off??  Thanks for your help!!

  • I would create  a Unique index with IGNORE_DUP_KEY option because when an attempt is made to enter data for which there is a unique index and the IGNORE_DUP_KEY clause is specified, only the rows violating the UNIQUE index fail. Try the links below for more info.  Hope this helps.

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_0bqr.asp

    http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=53&rl=1

    Kind regards,

    Gift Peddie

    Kind regards,
    Gift Peddie

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply