Create a rank column in a view

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

General SQL Server Forums

New to SQL Server Programming

Create a rank column in a view

Author

Topic

mjpr
Starting Member

7 Posts

Posted - 2008-01-14 : 15:08:13

Hi all!

I'm trying to script a view that does a simple query to some tables.
The catch is 2 of the columns are created on run time.
The first column is filled with a calculation with values of other columns. The 2nd column I would like to fill with the ranking of this calculated column

Example: (imagine caculatedcol is Sum(Col1+Col2+Col3)

Col1 Col2 Col3 CalculatedCol Ranking
10 10 10 30 1
9 9 9 27 2
8 8 8 24 3
7 7 7 21 4

How can I get the Ranking column filled based on the calculated column?

I'm desperate.
Thanks for any help.

---
Mário Ramos

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-14 : 15:32:57

If it is SQL Server 2005, you can use ROW_NUMBER() function, however I am not sure if it works for computed columns or not. In case of SQL Server 2000, creating on-the-fly ranking column will negatively affect performance if table data is huge.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-14 : 15:43:36

agreed about performance. The ranking function however seems to work on a derived column in a view:


create view test_v as
select id, id/2 as id2, row_number() over (order by id/2 desc) as rnk from sysobjects
go
select * from test_v where rnk between 10 and 20
go
drop view test_v

Be One with the Optimizer
TG

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-14 : 15:54:44

That's good then. If the calculation is more complex, probably OP can encapsulate calculated column inside a derived table.

create view test_v as
select 
id, id2, row_number() over (order by id2 desc) as rnk 
from (select id, id/2 as id2 from sysobjects) as Temp
go

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-01-14 : 18:19:41

You might want to use RANK() or DENSE_RANK() instead depending on what you want to do with equal values.

mjpr
Starting Member

7 Posts

Posted - 2008-01-15 : 05:58:05

Hi guys!
Forgot to mention that it's SQL Server 2000. So I don't have access to Row_number... :(

Any other ideas?

The table data is around 5000 rows. Not that much.

---
Mário Ramos

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-15 : 06:25:02

This code would work like normal rank fn:-

DECLARE @tt Table
(
Col1 int,
Col2 int,
Col3 int
)

INSERT INTO @tt VALUES (10,20,30)
INSERT INTO @tt VALUES (30,10,30)
INSERT INTO @tt VALUES (35,20,15)
INSERT INTO @tt VALUES (10,20,32)
INSERT INTO @tt VALUES (12,25,33)

--put into temp table results
SELECT Col1,Col2,Col3,Col1+Col2+Col3  as CalcCol INTO #temp
FROM @tt


SELECT *
FROM
(
SELECT Col1,Col2,Col3,CalcCol,
(SELECT COUNT(*) + 1 FROM #temp WHERE CalcCol>t.CalcCol) AS Rank
FROM #temp t)tmp
ORDER BY tmp.Rank 

output
-------
Col1        Col2        Col3        CalcCol     Rank
----------- ----------- ----------- ----------- -----------
30          10          30          70          1
35          20          15          70          1
12          25          33          70          1
10          20          32          62          4
10          20          30          60          5

mjpr
Starting Member

7 Posts

Posted - 2008-01-15 : 06:52:50

Hi!

That's ok, But I want to be able to have that in a view, not in a table.

---
Mário Ramos

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-15 : 07:13:32

I've managed to put my logic inside a view

CREATE VIEW MyView 
AS
--put into temp table results
SELECT *
FROM
(
SELECT Col1,Col2,Col3,CalcCol,
(SELECT COUNT(*) + 1 FROM 
(SELECT Col1,Col2,Col3,Col1+Col2+Col3  as CalcCol 
FROM tt)temp WHERE temp.CalcCol>t.CalcCol) AS Rank
FROM 
(SELECT Col1,Col2,Col3,Col1+Col2+Col3  as CalcCol 
FROM tt)t)tmp

GO

here tt represents your main table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources