June 6, 2006 at 3:11 am
I need Data in following Format... Basically i need ranking from sql.
Program Name | Cost | Ranking
Prog-A | 15000 | 4/4
Prog-B | 16000 | 3/4
Prog-C | 21000| 1/4
Prog-D |17000| 2/4
Now this is what i want .... as u can see there are 4 programs Prog-A,B,C,D.
and there ranking is based on the cost ... if Ranking is 4/4 it means record stands on 4th position out of 4 records ... if Ranking is 2/4 that means record is standing on 2nd position out of 4 records.
any clues plesae. basically i was supposed to do that in Crystal Reports but was unable to do that so i thought sql is a better choice.
please help me out here.
June 6, 2006 at 4:38 am
Something like this? You'd have to test it for performance if you have thousands or millions of programs. I haven't done the concatenation to show 1/4, 2/4 and so on - I think this is best done by your application. But it's quite easy to write should you want T-SQL to do it for you.
John
create table Programs (ProgName varchar(10), Cost int)
insert into Programs values ('Prog-A', 15000)
insert into Programs values ('Prog-B', 16000)
insert into Programs values ('Prog-C', 21000)
insert into Programs values ('Prog-D', 17000)
--drop table #Ranking
create table #Ranking (
RankID int identity(1,1),
ProgName varchar(10),
Cost int)
insert into #Ranking
select ProgName, Cost
from Programs
order by Cost desc
declare @Total int
select @Total = count(*) from #Ranking
Select ProgName as [Program Name],
Cost,
RankID as Ranking,
@Total as OutOf
from #Ranking
order by ProgName
June 6, 2006 at 4:47 am
declare @t table (prog varchar(8), cost int)
insert @t
select 'Prog-A', 15000 union all
select 'Prog-B' ,16000 union all
select 'Prog-C', 21000 union all
select 'Prog-D', 17000
select prog 'Program Name',
cost 'Cost',
convert(varchar, (select count(*) from @t z where z.cost >= t.cost)) + '/' + convert(varchar, (select count(*) from @t)) 'Ranking'
from @t t
N 56°04'39.16"
E 12°55'05.25"
June 6, 2006 at 9:39 am
Here is another solution with RANK() fn, introduced in SQL Server 2005.
declare @t table (prog varchar(8), cost int)
insert @t
select 'Prog-A', 15000 union all
select 'Prog-B' ,16000 union all
select 'Prog-C', 21000 union all
select 'Prog-D', 17000
SELECT
Prog,
Cost,
cast(RANK() OVER( Partition by 1 order by cost desc) as varchar) +'/'+ cast(a.Prog_count as varchar)
from @t b join (select count(1) as Prog_Count from @t) a
on 1 = 1
order by Prog
June 7, 2006 at 1:17 pm
thanks guys
approach by Rookie has some problems i used his approach and following problem occured:
Prog A 32.600187121827403 7/11
Prog B -416.6613067305484 11/11
Prog C 77.358114112521619 4/11
Prog D 77.358114112521619 4/11
Prog E 77.358114112521619 4/11
Prog F 45.474868447701986 5/11
Prog G 45.370487359437632 6/11
Prog I 92.406459085122478 1/11
Prog J -348.58557072146391 10/11
Prog X -264.27464536893552 9/11
Prog Z -16.706218187443774 8/11
as u can see there is no 2/11 and 3/11
June 7, 2006 at 1:26 pm
How would you like ties to be ranked?
N 56°04'39.16"
E 12°55'05.25"
June 7, 2006 at 10:54 pm
-- Populate test date
declare @t table (prog varchar(8), cost int)
insert @t
select 'Prog-A', 16000 union all
select 'Prog-B' ,16000 union all
select 'Prog-C', 21000 union all
select 'Prog-E', 15000 union all
select 'Prog-D', 17000
-- Do the work!
select prog 'Program Name',
cost 'Cost',
convert(varchar, 1 + (select count(*) from @t z where z.cost > t.cost)) + '/' + convert(varchar, (select count(*) from @t)) 'Ranking'
from @t t
Output from last example posted by uzwa ul haque
Program Name Cost Ranking
------------ --------------------------------- -----------------------------------------
Prog A 32.600187121827403 7/11
Prog B -416.6613067305484 11/11
Prog C 77.358114112521619 2/11
Prog D 77.358114112521619 2/11
Prog E 77.358114112521619 2/11
Prog F 45.474868447701986 5/11
Prog G 45.370487359437632 6/11
Prog I 92.406459085122478 1/11
Prog J -348.58557072146391 10/11
Prog X -264.27464536893552 9/11
Prog Z -16.706218187443774 8/11
(11 row(s) affected)
N 56°04'39.16"
E 12°55'05.25"
June 8, 2006 at 3:31 am
ur approach still has problem ... see
92.406459085122478 is 1/11
77.358114112521619 is number 2/11
so the other same entry 77.358114112521619 should be marked as 3/11
and 45.474868447701986 should be ranked 4/11 but its marked as 5/11
...
June 8, 2006 at 3:48 am
There are three 77.358114112521619
I ask you the same question again, how would you like ties (same numbers) to be ranked?
How do you differentiate between equal numbers?
N 56°04'39.16"
E 12°55'05.25"
June 8, 2006 at 3:56 am
Or do you simple want this?
-- Populate test data
declare @t table (prog varchar(8), cost float)
insert @t
select 'Prog A', 32.600187121827403 union all
select 'Prog B', -416.6613067305484 union all
select 'Prog C', 77.358114112521619 union all
select 'Prog D', 77.358114112521619 union all
select 'Prog E', 77.358114112521619 union all
select 'Prog F', 45.474868447701986 union all
select 'Prog G', 45.370487359437632 union all
select 'Prog I', 92.406459085122478 union all
select 'Prog J', -348.58557072146391 union all
select 'Prog X', -264.27464536893552 union all
select 'Prog Z', -16.706218187443774
-- Prepare output results
declare @results table (Rank INT IDENTITY(1, 1), Prog varchar(50), cost float)
INSERT @results (prog, cost)
select prog, cost from @t order by cost desc, prog
OUTPUT!
select Prog,
Cost,
CONVERT(VARCHAR, Rank) + '/' + CONVERT(VARCHAR, (SELECT COUNT(*) FROM @results)) Rank
from @results
Prog Cost Rank
-------- --------------------- ---------------------
Prog I 92.406459085122478 1/11
Prog C 77.358114112521619 2/11
Prog D 77.358114112521619 3/11
Prog E 77.358114112521619 4/11
Prog F 45.474868447701986 5/11
Prog G 45.370487359437632 6/11
Prog A 32.600187121827403 7/11
Prog Z -16.706218187443774 8/11
Prog X -264.27464536893552 9/11
Prog J -348.58557072146391 10/11
Prog B -416.6613067305484 11/11
What you want is not ranking, it is sorting. See also with John Mitchell posted to you before.
N 56°04'39.16"
E 12°55'05.25"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply