Ranking within Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following code to rank my Tech's.

SELECT a.String, COUNT(*) AS rank
FROM 04_qry_MTD_Tech_Rank_Calculation AS a INNER JOIN
04_qry_MTD_Tech_Rank_Calculation AS b ON a.Missed_Percent>=b.Missed_Percent
GROUP BY a.String;

However, the issue is that if there are 159 Tech's with the same percentage
then the rank for each tech is 159. What I would like to do is that if there
is 159 Tech's that are ranked 1st then the rank to display would be 1 instead
of 159.

For example, using the current String I end up with:

Tech String Rank
120 159
130 159
150 160
160 160

The desired result would be:

Tech String Rank
120 1
130 1
150 2
160 2

Any help would be greatly appreciated!!
 
Michael said:
I have the following code to rank my Tech's.

SELECT a.String, COUNT(*) AS rank
FROM 04_qry_MTD_Tech_Rank_Calculation AS a INNER JOIN
04_qry_MTD_Tech_Rank_Calculation AS b ON a.Missed_Percent>=b.Missed_Percent
GROUP BY a.String;

However, the issue is that if there are 159 Tech's with the same percentage
then the rank for each tech is 159. What I would like to do is that if there
is 159 Tech's that are ranked 1st then the rank to display would be 1 instead
of 159.
Click to expand...

Does this work any better?

SELECT a.string, a.Missed_Percent,
(SELECT COUNT(*)
FROM 04_qry_MTD_Tech_Rank_Calculation AS b
WHERE a.Missed_Percent <= b.Missed_Percent
AND a.String = b.String) AS rank
FROM 04_qry_MTD_Tech_Rank_Calculation AS a
 
Neil,

Thanks for taking the time to answer my post but all Records ended a Rank 1.

Any other ideas on how to accomplish the desired result?
 
Michael said:
Thanks for taking the time to answer my post but all Records ended a Rank 1.

Any other ideas on how to accomplish the desired result?
Click to expand...

Oops, try this instead:

SELECT a.string, a.Missed_Percent,
(SELECT COUNT(*)
FROM 04_qry_MTD_Tech_Rank_Calculation AS b
WHERE a.Missed_Percent <= b.Missed_Percent) AS rank
FROM 04_qry_MTD_Tech_Rank_Calculation AS a
 
Neil,

I waited over 15 minutes to see the result of the change. Is there any
other way you can think of that would not take as long to get the result?

Either way, thank you again!!
 
Michael said:
I waited over 15 minutes to see the result of the change. Is there any
other way you can think of that would not take as long to get the result?
Click to expand...

How many records are in that table?!?

I doubt it will make a difference, but you could try this:

SELECT a.string, a.Missed_Percent,
(SELECT COUNT(Missed_Percent)
FROM 04_qry_MTD_Tech_Rank_Calculation AS b
WHERE a.Missed_Percent <= b.Missed_Percent) AS rank
FROM 04_qry_MTD_Tech_Rank_Calculation AS a
 
I am 20 minutes into doing a similar query on over 30000 records. I expect a
vastly different result when I index the field.
 
David...

Indexing the field worked fantastic on improving the speed of the query.
However, I still did not get the desired result. I still have a rank that
show's 158 employees in 1st place but ranked as 158 instead of 1. Any ideas
how to correct this?

Thanks in advanced!
 
David,

Thanks for your reply. Indexing definately resolved the speed issue with
the query. However, I still did not get the result I was looking for. I
currently have 158 employees tied for 1st place but with the code below I get
all 158 employees ranked as 158 instead of 1. Any ideas?

Thanks in advanced!
 
PMFBI

try

SELECT a.string, a.Missed_Percent,
(SELECT COUNT(Missed_Percent)
FROM 04_qry_MTD_Tech_Rank_Calculation AS b
WHERE a.Missed_Percent < b.Missed_Percent) + 1 AS rank
FROM 04_qry_MTD_Tech_Rank_Calculation AS a
 
SELECT tblMembers.lngMemberID, (select count(*) +1 as s from tblmembers as x
where(x.lngmemberid > tblmembers.lngmemberid)) AS rank
FROM tblMembers
WHERE ((([tblMembers.lngMemberID])>300000));

The where clause is to select a sample. I am working with the 2007 Beta. I
have run into all sorts of odd problems which I do not know the reason for.

The query above works with 175 records that do not have any duplicates in
the data set. With a larger sample there are records missing, but the
records that are there are ranked correctly.

I have not been able to sort this query, the SQL gives an error message.

I can use it as an input to another query and sort it there and all looks
beautiful.

The quest continues.
 
And if I try it with duplicate data it fails. At the moment I have no idea
why.


David F Cox said:
SELECT tblMembers.lngMemberID, (select count(*) +1 as s from tblmembers as
x where(x.lngmemberid > tblmembers.lngmemberid)) AS rank
FROM tblMembers
WHERE ((([tblMembers.lngMemberID])>300000));

The where clause is to select a sample. I am working with the 2007 Beta. I
have run into all sorts of odd problems which I do not know the reason
for.

The query above works with 175 records that do not have any duplicates in
the data set. With a larger sample there are records missing, but the
records that are there are ranked correctly.

I have not been able to sort this query, the SQL gives an error message.

I can use it as an input to another query and sort it there and all looks
beautiful.

The quest continues.


Michael said:
David,

Thanks for your reply. Indexing definately resolved the speed issue with
the query. However, I still did not get the result I was looking for. I
currently have 158 employees tied for 1st place but with the code below I
get
all 158 employees ranked as 158 instead of 1. Any ideas?

Thanks in advanced!
Click to expand...
Click to expand...
 
One major problem was the lack of a WHERE clause in the count subquery. I
have included the WHERE MemID clauses. You may not need them, they are there
to limit the number of records.

This actually gives the desired results with multiple records with the same
rank.

SELECT TM.YourFieldToRank, (select count(*) +1 as s from TM as x
where(x.YourFieldToRank< TM.YourFieldToRank) AND [x.MemID]> 300000) AS rank
FROM TM
WHERE ((([TM.MemID])>300000))
ORDER BY TM.YourFieldToRank;




David F Cox said:
SELECT tblMembers.lngMemberID, (select count(*) +1 as s from tblmembers as
x where(x.lngmemberid > tblmembers.lngmemberid)) AS rank
FROM tblMembers
WHERE ((([tblMembers.lngMemberID])>300000));

The where clause is to select a sample. I am working with the 2007 Beta. I
have run into all sorts of odd problems which I do not know the reason
for.

The query above works with 175 records that do not have any duplicates in
the data set. With a larger sample there are records missing, but the
records that are there are ranked correctly.

I have not been able to sort this query, the SQL gives an error message.

I can use it as an input to another query and sort it there and all looks
beautiful.

The quest continues.


Michael said:
David,

Thanks for your reply. Indexing definately resolved the speed issue with
the query. However, I still did not get the result I was looking for. I
currently have 158 employees tied for 1st place but with the code below I
get
all 158 employees ranked as 158 instead of 1. Any ideas?

Thanks in advanced!
Click to expand...
Click to expand...
 
Back
Top