Storing Rank - 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

General SQL Server Forums

New to SQL Server Programming

Storing Rank

Author

Topic

McNoob
Starting Member

3 Posts

Posted - 2008-12-05 : 05:30:38

How can i store the rank obtained using
row_number OVER( order by ColumnName desc)

I tried to make a spare column to store rank called rankin.
Then i tried using 'update set' command but got error when i used row_number() inside the set boundary.

Table format:
Nam A B C D E rankin

Name is varchar(20)
Others are int

I want to query the table on all A B C D E separating and append the ranks i obtain in each query to the rankin column correspondingly.

I tried using a variable to store, but all fields of rankin column get same value at the end of query.

Please help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-05 : 05:57:07

See if you get required result

select Nam, A ,B, C, D, E, row_number(partition by A, B, C, D, E) as ranking From your_table

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-05 : 06:04:11

Oh -- I though OP wanted to store the rank inside the parent table. To do that you'd need to use a derived table.


IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test

CREATE TABLE #test (
	[name] VARCHAR(255)
	, [a] VARCHAR(50)
	, [b] VARCHAR(50)
	, [c] VARCHAR(50)
	)

INSERT #test ([name], [a], [b], [c])
      SELECT 'foo', 'a', 'b', 'c'
UNION SELECT 'bar', 'e', 'f', 'g'
UNION SELECT 'moo', '1', '2', '3'
UNION SELECT 'woo', '4', '5', '6'

ALTER TABLE #test ADD [rankA] INT NULL

UPDATE t SET
	[rankA] = o.[rank]
FROM
	#test t
	JOIN (
		SELECT
			[name] AS [oname]
			, ROW_NUMBER() OVER (ORDER BY [a] ASC) AS [rank]
		FROM
			#test
		)
		o ON o.[oname] = t.[name]

SELECT * FROM #test ORDER BY [rankA] DESC

This assumes that [name] is the key to the table

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

McNoob
Starting Member

3 Posts

Posted - 2008-12-05 : 06:12:41

Thank-you very much Transact charlie for the detailed quick reply along with code.

I am really noob so would take a while to check out the code and concepts involved.

Shall post back.

Btw, charlie is right, i need to store accumulated ranks in parent table. The Nam coloumn uniquely identifies all rows. There are no repetitions possible even in future.

McNoob
Starting Member

3 Posts

Posted - 2008-12-05 : 07:15:35

Thanks again Transact Charlie, i was finally able to obtain the result i needed.
Although, i wish it could be done by some procedure , which avoids the requirement of extra columns and temporary tables.

IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test

CREATE TABLE #test (
	 Nam VARCHAR(255),
	 A int,
     B int,
     C int
)


INSERT #test ([Nam],[A],[B],[C])
      SELECT 'foo', '1', '2', '3'
UNION SELECT 'bar', '56', '89', '8'
UNION SELECT 'moo', '14', '33', '6'
UNION SELECT 'woo', '4', '5', '6'

ALTER TABLE #test ADD [rankA] INT NULL
ALTER TABLE #test ADD [rankC] INT NULL
ALTER TABLE #test ADD [rankB] INT NULL
ALTER TABLE #test ADD [rankfin] INT NULL

UPDATE t SET
	[rankA] = o.[rank]
FROM
	#test t
	JOIN (
		SELECT
			[nam] AS [onam]
			, ROW_NUMBER() OVER (ORDER BY [A] desc) AS [rank]
		FROM
			#test
		)
		o ON o.[onam] = t.[nam]

UPDATE t SET
	[rankB] = p.[rank]
FROM
	#test t
	JOIN (
		SELECT
			[nam] AS [onam]
			, ROW_NUMBER() OVER (ORDER BY [B] desc) AS [rank]
		FROM
			#test
		)
		p ON p.[onam] = t.[nam]

UPDATE t SET
	[rankC] = q.[rank]
FROM
	#test t
	JOIN (
		SELECT
			[nam] AS [onam]
			, ROW_NUMBER() OVER (ORDER BY [C] ASC) AS [rank]
		FROM
			#test
		)
		q ON q.[onam] = t.[nam]

update t set [rankfin]= [rankA] + [rankB] + [rankC]
from #test t



SELECT * FROM #test

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-05 : 11:26:14

Well yes you *could* do it like that. Or you could do this:


IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test

CREATE TABLE #test (
	 Nam VARCHAR(255),
	 A int,
     B int,
     C int
)


INSERT #test ([Nam],[A],[B],[C])
      SELECT 'foo', '1', '2', '3'
UNION SELECT 'bar', '56', '89', '8'
UNION SELECT 'moo', '14', '33', '56'
UNION SELECT 'woo', '4', '5', '6'

ALTER TABLE #test ADD [rankA] INT NULL
ALTER TABLE #test ADD [rankC] INT NULL
ALTER TABLE #test ADD [rankB] INT NULL
ALTER TABLE #test ADD [rankfin] INT NULL

UPDATE t SET
	[rankA] = o.[rankA]
	, [rankB] = o.[rankB]
	, [rankC] = o.[rankC]
	, [rankFin] = o.[rankA] + o.[rankB] + o.[rankC]
FROM
	#test t
	JOIN (
		SELECT
			[nam] AS [onam]
			, ROW_NUMBER() OVER (ORDER BY [A] desc) AS [rankA]
			, ROW_NUMBER() OVER (ORDER BY [B] desc) AS [rankB]
			, ROW_NUMBER() OVER (ORDER BY [C] desc) AS [rankC]
		FROM
			#test
		)
		o ON o.[onam] = t.[nam]

SELECT * FROM #test

Which will generate the same result

NB :: I changed some of your sample data as before all the ranks for A, B, C resulted in the same order

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources