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.
| Author |
Topic |
|
Japboix1
Starting Member
24 Posts |
 Posted - 2011-07-08 : 11:03:29
|
| I really need help with this. I want to assign an id to every rwo in my database. I want to basically have a function look through one of my columns, and give an id to every unique entry. I have been able to do this with the dense rank function. However, now, I want to know if it is possible to have it check two columns for unique entries. Basically, for every row, my function would check to see if the entry is unique. If it is, it woudl then check a second column to see if the entry in that column is unique as well. That way, it would only give the entry a new id number if both columns are unique. Also, I don't want the columns that aren't unique to be deleted. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-07-08 : 11:06:43
|
| You can add that column in the partition by clauseMadhivananFailing to plan is Planning to fail |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-08 : 11:08:41
|
| doesn't this do it?select col1, col2, id = rank() over (order by col1, col2)from tbl==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Japboix1
Starting Member
24 Posts |
Posted - 2011-07-08 : 11:36:52
|
| @nigelrivettNo, it doesn't check the second column. It only ranks it based on the first column I enter.@madhivananThe parition by clause restarts the rankings for each new entry in the second column I want checked. I have a couple thousand entries in my database and I need each unique entry to have it's own id so the partition by clause doesn't really help with that. |
|
|
|
|
|
|
|
|
Dense rank