Ranking Result sets in TSQL
-
Guys,
Does anybody know if there is a function that can be used to rank the results sets in TSQL?
I have had a quick look and could only find the CONTAINSTABLE function which doesn't do what I need.
Regards
Carl
-
Carl - could you give a few more details about what you mean by ranking.
CONTAINSTABLE is used with full text searching - the ranking in that gives you the relative ranking of results based on the contains query (so only relevant within a single CONTAINSTABLE set of results), but there isn't anything else in there afik.
Jon
-
Hi,
One of our reporting team wants to be able to rank a result set so the row with the highest amount of sales for each month is shown as number 1 in the ranking, etc.
For instance, if there are 10 members of a sales team we want to be able to rank the team on a monthly basis on sales made.
Regards
Carl
-
Here is one way to do a rank:
Taken from The Guru's Guide to Transact SQL pg. 190
select ranking=identity(int), column1
into #rank from table where 1=0 --creates empty table
insert #rank (column1)
select column1 from table order by column1 desc
select * from #rank order by rank
drop table #rank
..note this doesn't asign the same rank for a tie
-
A few variant possibilities - obviously this code, like my brain, comes with no warranty
set nocount on
create table #salesPerson (SPId int, SalesPerson varchar(10))
create table #sales (SPId int, amount decimal(8,2))
insert into #salesPerson values(1, 'fred')
insert into #salesPerson values(2, 'joe')
insert into #salesPerson values(3, 'anne')
insert into #salesPerson values(4, 'mark')
insert into #sales values(1, 10)
insert into #sales values(2, 100)
insert into #sales values(3, 550.50)
insert into #sales values(2, 1000.05)
insert into #sales values(1, 999.95)
insert into #sales values(1, 0.05)
insert into #sales values(4, 1010)
----
select p.SalesPerson, sum(s.amount) TotalSales
from #salesPerson p
join #sales s
on s.SPId = p.SPId
group by p.SalesPerson
order by sum(s.amount) desc
--
declare @results table(Ranker int identity not null, SalesPerson varchar(10), TotalSales decimal(15,2))
insert into @results (SalesPerson, TotalSales)
select p.SalesPerson, sum(s.amount)
from #salesPerson p
join #sales s
on s.SPId = p.SPId
group by p.SalesPerson
order by sum(s.amount) desc
select * from @results
--
select (select count(*)+1 from
(
select sum(s.amount) TotalSales
from #salesPerson p
join #sales s
on s.SPId = p.SPId
group by p.SalesPerson) c
where c.TotalSales > t.TotalSales) Ranker,
t.SalesPerson,
t.TotalSales
from (
select p.SalesPerson, sum(s.amount) TotalSales
from #salesPerson p
join #sales s
on s.SPId = p.SPId
group by p.SalesPerson) t
order by Ranker
----
drop table #sales
drop table #salesPerson
set nocount off
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply