Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: rank the employees by count and amount (was "query help")

    I have a query that returns a result that looks like this:

    amount count
    steve 122000 12
    jim 145213 13
    paul 62325 7

    I need to add 2 columns to this query that rank the employees by count and amount with a number, so for example:

    amount amount_rank count count_rank
    steve 122000 2 12 2
    jim 145213 1 13 1
    paul 62325 3 7 3

    I was going to paste the entire query in but I thought I'd try this first to see if I could get by with a hint. So essentially I need to evaluate with an order by or something and then insert a number as amount_rank and number as count_rank.

    Any ideas?
    Thanks,
    Bill

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ok, here's a hint:

    You can either use correlated subqueries in the SELECT clause, or use two self-joins with an aggregate query to count disting pkeys.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Posts
    269
    see this example, I assume employee name is the pkey here.Im not sure how will u handle if amount is equal or count is equal.
    So i included both option here ,ranking and dense ranking.
    First code will give ranking
    Code:
    create table #employeeRank 
    (
    amount int,
    name varchar(20),
    counts int
    )
    insert into #employeeRank 
    select 2000,'AAA',12
    union
    select 3700,'BBB',5
    union
    select 3433,'dfds',9
    union
    select 9000,'CCC',1
    union
    select 1000,'fdfd', 3
    union
    select 200,'joe',8
    union
    select 5600,'tim',8
    union
    select 2000,'foo',7
    select #employeeRank.*,amount_rank,counts_rank from #employeeRank join
    (
    select top 100 percent name,(select count(*)+1 from #employeeRank d1 where d1.amount>d.amount ) as amount_rank
    from #employeeRank d order by amount desc) as am
    on #employeeRank.name=am.name join
    (
    select top 100 percent name ,(select count(*)+1 from #employeeRank d1 where d1.counts>d.counts ) as counts_rank
    from #employeeRank d order by counts desc
    ) as co
    on #employeeRank.name=co.name 
    order by #employeeRank.amount desc
    this will give u dense ranking
    Code:
    create table #employeeRank 
    (
    amount int,
    name varchar(20),
    counts int
    )
    insert into #employeeRank 
    select 2000,'AAA',12
    union
    select 3700,'BBB',5
    union
    select 3433,'dfds',9
    union
    select 9000,'CCC',1
    union
    select 1000,'fdfd', 3
    union
    select 200,'joe',8
    union
    select 5600,'tim',8
    union
    select 2000,'foo',7
    select #employeeRank.*,amount_rank,counts_rank from #employeeRank join
    (
    select top 100 percent name,(select count(*) from #employeeRank d1 where d1.amount>=d.amount ) as amount_rank
    from #employeeRank d order by amount desc) as am
    on #employeeRank.name=am.name join
    (
    select top 100 percent name ,(select count(*) from #employeeRank d1 where d1.counts>=d.counts ) as counts_rank
    from #employeeRank d order by counts desc
    ) as co
    on #employeeRank.name=co.name 
    order by #employeeRank.amount desc
    Last edited by mallier; 05-12-06 at 06:06.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    These two methods avoid some of the nested subqueries and sorts, and execute more efficiently:
    Code:
    select	#EmployeeRank.Amount,
    	#EmployeeRank.Name,
    	#EmployeeRank.Counts,
    	(select count(*) from #EmployeeRank Amount_Ranks where Amount_Ranks.Amount > #EmployeeRank.Amount) + 1as Amount_Rank,
    	(select count(*) from #EmployeeRank Count_Ranks where Count_Ranks.Counts > #EmployeeRank.Counts) + 1 as Count_Rank
    from	#EmployeeRank
    order by #EmployeeRAnk.Amount DESC
    
    select	#EmployeeRank.Amount,
    	#EmployeeRank.Name,
    	#EmployeeRank.Counts,
    	count (distinct Amount_Ranks.Name) + 1 as Amount_rank,
    	count (distinct Count_Ranks.Name) + 1as Count_rank
    from	#EmployeeRank
    	left outer join #EmployeeRank Amount_Ranks on #EmployeeRank.Amount < Amount_Ranks.Amount
    	left outer join #EmployeeRank Count_Ranks on #EmployeeRank.Counts < Count_Ranks.Counts
    group by #EmployeeRank.Amount,
    	#EmployeeRank.Name,
    	#EmployeeRank.Counts
    order by #EmployeeRAnk.Amount DESC
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •