Create unique ID temporarily - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Create unique ID temporarily

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-10 : 09:02:21

Jenny writes "Hi!
This is a sqlstatment
select k.fileid, k.name, a.payday
from a
inner join g on g.gnid = a.gnid
inner join k on k.fileid = g.fileid

The output could be

2455 Jenny 2001-01-31
4533 Jenny 2002-02-28
5744 Henrik 2001-01-31
1995 Henrik 2000-12-31
9076 Jenny 2002-03-31
etc

This will pick out name, fileid and payday for salaries. I would to temporarily add a unique numeric id connected to the name. Since the primary unique key in table k is the name, I need to keep it like that in the DB, but in this query I want it to be like

2455 Jenny 2001-01-31 11
4533 Jenny 2002-02-28 11
5744 Henrik 2001-01-31 13
1995 Henrik 2000-12-31 13
9076 Jenny 2002-03-31 11
where 11 and 13 would be the generated id´s.

Hope you have a smart solution for me.

Jenny"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-07-10 : 09:53:15

Could put distinct names into a temp table with an identity then join to that for the output
or

select k.fileid, k.name, a.payday, (select count(*) from k k2 where k2.name <= k.name)
from a
inner join g on g.gnid = a.gnid
inner join k on k.fileid = g.fileid ) as a

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources