September 23, 2005 at 8:18 am
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!!
September 26, 2005 at 1:47 am
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