Problem with Assigning Values - 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

Transact-SQL (2000)

Problem with Assigning Values

Author

Topic

Lopaka
Starting Member

48 Posts

Posted - 2006-03-16 : 17:11:32

I am trying to assign values to a unique record set(s). All of the records are in one table, for example:

Table:
PK, NAME, BASE, RANK
1, AAA, 1.50, NULL
2, AAA, 2.25, NULL
3, AAA, 1.55, NULL
4, AAA, 1.00, NULL
5, AAA, 1.99, NULL
6, AAA, 2.10, NULL
7, BBB, 1.00, NULL
8, BBB, 1.50, NULL
9, CCC, 1.01, NULL
10, CCC, 1.03, NULL
11, CCC, 1.02, NULL

My current query pulls all of the ‘AAA’ values. Sorts by base descending order. Writes the data to a temporary table with an identity column defined. Then I join the table and the temp table based on pk and name, then update Table.Rank with the temp table values. Then I repeat the process.

END Result:
PK, NAME, BASE, RANK
1, AAA, 1.50, 5
2, AAA, 2.25, 1
3, AAA, 1.55, 4
4, AAA, 1.00, 6
5, AAA, 1.99, 3
6, AAA, 2.10, 2
7, BBB, 1.00, 2
8, BBB, 1.50, 1
9, CCC, 1.01, 3
10, CCC, 1.03, 1
11, CCC, 1.02, 2

The problem with my query is the amount of data, 500k Unique Names. Each name can consist of up to 10 base values. Is there a fast way of assigning the RANK?

Thank you.

Robert R. Barnes

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-16 : 17:23:00

Here is one way:


set nocount on

create table #table (PK int, NAME varchar(3), BASE money, RANK int null)
insert #table 
select 1, 'AAA', 1.50, null union all
select 2, 'AAA', 2.25, null union all
select 3, 'AAA', 1.55, null union all
select 4, 'AAA', 1.00, null union all
select 5, 'AAA', 1.99, null union all
select 6, 'AAA', 2.10, null union all
select 7, 'BBB', 1.00, null union all
select 8, 'BBB', 1.50, null union all
select 9, 'CCC', 1.01, null union all
select 10, 'CCC', 1.03, null union all
select 11, 'CCC', 1.02, null 


update a set
	a.rank = (select count(*) from #table where [name] = a.[name] and base <= a.base)
from	#table a

select * from #table order by name, rank --and serial number :)

output:
PK          NAME BASE                  RANK        
----------- ---- --------------------- ----------- 
4           AAA  1.0000                1
1           AAA  1.5000                2
3           AAA  1.5500                3
5           AAA  1.9900                4
6           AAA  2.1000                5
2           AAA  2.2500                6
7           BBB  1.0000                1
8           BBB  1.5000                2
9           CCC  1.0100                1
11          CCC  1.0200                2
10          CCC  1.0300                3

EDIT:
How do you want to rank ties?

Be One with the Optimizer
TG

Lopaka
Starting Member

48 Posts

Posted - 2006-03-16 : 17:38:53

It worked great, thank you...:)

Robert R. Barnes

Lopaka
Starting Member

48 Posts

Posted - 2006-03-16 : 18:11:37

This only works if you have values in the base field. when there are duplicate or null values it repeats...

Any ideas?

Robert R. Barnes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-16 : 20:28:35

"This only works if you have values in the base field. when there are duplicate or null values it repeats..."

quote:
Originally posted by TG

How do you want to rank ties?

You did not answer TG's question.
And also How do you want to treat null values as ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-17 : 00:27:10

Where do you want to show the data?
If you use Reports, then Group it by Keycolumn then make use of Running Total with Count and reset it to group level. Trying to do this from SQL will become ineffecient if there are millions of rows on that table

Madhivanan

Failing to plan is Planning to fail

Lopaka
Starting Member

48 Posts

Posted - 2006-03-17 : 01:23:08

Ties and nulls are to be treated secquencially as well. The base is the primary sort and if null does not matter what number comes next.

Robert R. Barnes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-17 : 02:06:01

[code]set nocount on

create table #table (PK int, [NAME] varchar(3), BASE money, RANK int null)

insert #table
select 1, 'AAA', 1.50, null union all
select 2, 'AAA', 2.25, null union all
select 3, 'AAA', 1.55, null union all
select 4, 'AAA', 1.00, null union all
select 5, 'AAA', 1.99, null union all
select 6, 'AAA', 2.10, null union all
select 7, 'BBB', 1.00, null union all
select 8, 'BBB', 1.50, null union all
select 9, 'CCC', 1.01, null union all
select 10, 'CCC', 1.03, null union all
select 11, 'CCC', 1.02, null union all
select 12, 'AAA', 1.55, null union all
select 13, 'AAA', null, null union all
select 14, 'CCC', 1.02, null union all
select 15, 'CCC', 1.02, null union all
select 16, 'CCC', 1.02, null union all
select 17, 'BBB', 1.00, null union all
select 18, 'BBB', 1.00, null

update a
set
a.RANK = (select count(*) from #table where [NAME] = a.[NAME] and isnull(BASE, 0) <= isnull(a.BASE, 0))
from #table a

update t
set t.RANK = t.RANK - (select count(*) from #table x where x.[NAME] = t.[NAME] and x.RANK = t.RANK and x.PK < t.PK)
from #table t
inner join
(
select [NAME], RANK
from #table
group by [NAME], RANK
having count(*) > 1
) m
on t.[NAME] = m.[NAME]
and t.RANK = m.RANK

select * from #table order by [NAME], RANK

drop table #table[/code]

Result :
[code]PK NAME BASE RANK
----------- ---- --------------------- -----------
13 AAA NULL 1
4 AAA 1.0000 2
1 AAA 1.5000 3
12 AAA 1.5500 4
3 AAA 1.5500 5
5 AAA 1.9900 6
6 AAA 2.1000 7
2 AAA 2.2500 8
18 BBB 1.0000 1
17 BBB 1.0000 2
7 BBB 1.0000 3
8 BBB 1.5000 4
9 CCC 1.0100 1
16 CCC 1.0200 2
15 CCC 1.0200 3
14 CCC 1.0200 4
11 CCC 1.0200 5
10 CCC 1.0300 6[/code]

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources