• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

Compute a ranking based on multiple columns and order the selection by that ranking

I have a table defined as
Create Table MyTest (ID int Primary Key, MyType varchar(10), Criterion1 int, Criterion2 int)
Insert Into MyTest (ID, MyType, Criterion1, Criterion2) Values (1,'One',42,4)
Insert Into MyTest (ID, MyType, Criterion1, Criterion2) Values (2,'One',43,5)
Insert Into MyTest (ID, MyType, Criterion1, Criterion2) Values (3,'One',44,3)
Insert Into MyTest (ID, MyType, Criterion1, Criterion2) Values (4,'Two',24,7)
Insert Into MyTest (ID, MyType, Criterion1, Criterion2) Values (5,'Two',25,8)
Insert Into MyTest (ID, MyType, Criterion1, Criterion2) Values (6,'Two',26,6)

I'd like to select from this table ordered by a ranking.
For example (1,'One',42,4) would rank 3rd if selected by
     Select MyType,Criterion1 from MyTest Order By Criterion1 Desc
and would rank 5th if selected by
     Select MyType,Criterion2 from MyTest Order By Criterion2 Desc
I'd like to compute (1,'One',42,4)'s rank as (3+5)/2 and select in order of that rank.

How can I do that?
0
josgood
Asked:
josgood
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

select l.*
select id, mytype, criterion1, criterion2
, ( select count(*) from MyTest i where i.mytype = t.mytype and i.criterion1 <= t.criterion1 ) rank1
, ( select count(*) from MyTest i where i.mytype = t.mytype and i.criterion2 <= t.criterion2 ) rank2
from MyTest t
) l
order by (l.rank1 + l.rank2) / 2

note: the /2 is actually not needed :)
0
 
josgoodAuthor Commented:
Thank you for the quick answer!

Running this query, I get
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, missed the FROM(  part in the query:


select l.*
from (
select id, mytype, criterion1, criterion2
, ( select count(*) from MyTest i where i.mytype = t.mytype and i.criterion1 <= t.criterion1 ) rank1
, ( select count(*) from MyTest i where i.mytype = t.mytype and i.criterion2 <= t.criterion2 ) rank2
from MyTest t
) l
order by (l.rank1 + l.rank2) / 2
0
 
josgoodAuthor Commented:
Beautiful !!  Thank you!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now