Rank on columns - 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 2008 Forums

Transact-SQL (2008)

Rank on columns

Author

Topic

eureka18
Starting Member

3 Posts

Posted - 2015-01-05 : 08:40:54

Hi All,

I want to do a rank on columns instead of rows. Reason is that I have 10 different columns with values in it which need to be ranked based on their value (higher value should get lower rank). The rank function in SQL only works on rows and not on columns.

As an example I have the following table:
Column 1, COlumn 2, Column 3
3, 6, 4

The rank of this should be

3, 1, 2

is such a thing possible?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-05 : 08:57:54

This worked for me:


declare  @t table(Column1 int , COlumn2 int , Column3 int)

insert into @t values (3, 6, 4)

select p.[1],p.[2],p.[3] from @t t
cross apply (
	select colval
		, rn = ROW_NUMBER() over(order  by colval)  
	from (values (column1), (column2), (column3) ) r(colval)
	) r
pivot (max(colval) for rn in ([1],[2],[3])) p

eureka18
Starting Member

3 Posts

Posted - 2015-01-05 : 12:02:40

I needed to update the query below to work with my data. This is what i got:


select ID, Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9
into #t
From #OriginalTable

select ID as IDTAG, p.[1],p.[2],p.[3],p.[4],p.[5], p.[6],p.[7],p.,p.[9] into #u from #t
cross apply (
	select colval
		, rn = ROW_NUMBER() over(order  by colval)  
	from (values (Column1), (Column2), (Column3), (Column4), (Column5), (Column6), (Column7), (Column8), (Column9)) r(colval)
) r
pivot (max(colval) for rn in ([1],[2],[3],[4],[5],[6],[7],,[9])) p

However,this works up until the data in the cell <= 9 as it sees the cells as string. For values >9 it doesn't work.

How can I fix that?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-05 : 12:09:07

Well, you never said that the datatype was character!

Change it in the order by:

order by cast(colval as int)

Hope all your columns are valid numbers!

eureka18
Starting Member

3 Posts

Posted - 2015-01-06 : 04:13:20

works like a charm! thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources