alternative row_number...help tnx

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

Transact-SQL (2000)

alternative row_number...help tnx

Author

Topic

artbishop
Starting Member

18 Posts

Posted - 2009-02-19 : 00:38:20

How do i get the following result:

RANKID NAMEID NAME
0 1 Alfred
1 2 Jen
2 5 Amber
2 3 Maricel
1 4 Philip

if I have two SQL database table:

first table is dbo.NAME

ID NAME
1 Alfred
2 Jen
3 Maricel
4 Philip
5 Amber

then second table is RANK

NAMEID RANKID
1 0
2 1
3 2
4 1
5 2

thanks

here's the formula...

SELECT dbo.T_Relationships.PARENT_NAMEID, dbo.T_Relationships.NAMEID, dbo.T_Names.NAME
FROM (SELECT ROW_NUMBER() OVER (partition BY dbo.T_Relationships.PARENT_NAMEID
ORDER BY NAME) AS seq, dbo.T_Relationships.PARENT_NAMEID, dbo.T_Relationships.NAMEID, dbo.T_Names.NAME
FROM dbo.T_Names INNER JOIN
dbo.T_Relationships ON dbo.T_Names.ID = dbo.T_Relationships.NAMEID) Z
ORDER BY seq

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-19 : 00:54:46

dont post duplicates ,

try this

declare @Name Table ( ID int, NAME varchar(32))
insert into @name
select 1, 'Alfred' union all
select 2 ,'Jen' union all
select 3 ,'Maricel' union all
select 4 ,'Philip' union all
select 5 ,'Amber'

declare @rank table (NAMEID int, RANKID int)
insert into @rank
select 1, 0 union all
select 2, 1 union all
select 3, 2 union all
select 4, 1 union all
select 5, 2

declare @cnt Int
select @cnt = count(*) from @name

select r.rankid,n.id,n.name
from @name n
inner join @rank r on r.nameid = n.id
order by case when id = (select max(id) from @name) then id-((@cnt/2)+1) else id end

artbishop
Starting Member

18 Posts

Posted - 2009-02-19 : 01:07:44

oppps sorry about that...thanks Nageswar9

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-19 : 01:26:36

quote:
Originally posted by artbishop

oppps sorry about that...thanks Nageswar9

ok, Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources