SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 generate a unique key for 3 columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zoe2003
Starting Member

15 Posts

Posted - 01/07/2013 :  05:20:10  Show Profile  Reply with Quote
Hello all,

I need help in generating a unique key for 3 columns in a view.
For example : I have a view that returns those columns:
f1 f2 f3
111 222 333
111 222 333
aaa bbb ccc
aaa bbb ddd
aaa bbb ccc
111 222 333

I want to add another field that will be a unique key for those 3 fields, like that :
key f1 f2 f3
1 111 222 333
1 111 222 333
2 aaa bbb ccc
3 aaa bbb ddd
2 aaa bbb ccc
1 111 222 333

How can I do it ?

I appreciate your help.

Thanks,
Z.

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/07/2013 :  05:34:04  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
select a.seq, b.*
from
(
select distinct f1.f2,f3 seq = row_number() from tbl
) a
join tbl b
on a.f1 = b.f1 and a.f2 = b.f2 and a.f3 = b.f3

==========================================
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.
Go to Top of Page

sivas2k8
Starting Member

India
1 Posts

Posted - 01/07/2013 :  06:28:39  Show Profile  Reply with Quote
Try this. Sure you'll find the solution here..
http://sivanandsql.blogspot.in/2013/01/genenerating-unique-key-for-3-columns.html
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/07/2013 :  09:42:04  Show Profile  Reply with Quote

select *
from
(
select dense_rank() over (order by f1,f2,f3) AS key,*
from table
)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

zoe2003
Starting Member

15 Posts

Posted - 01/07/2013 :  10:23:33  Show Profile  Reply with Quote
Thank you all !
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/07/2013 :  10:33:51  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000