Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Separate Ties In Ranking Query

Status
Not open for further replies.

lars7

Technical User
Aug 16, 2005
817
GB
Hi Guys,
I'm using the code below to rank groups of records in a query:

SELECT A.[Pay Number], A.[NI Number], A.[Contracted Hours], Count(*) AS Rank
FROM tbl1 AS A LEFT JOIN tbl1 AS B ON (A.[Contracted Hours]<= B.[Contracted Hours]) AND (A.[NI Number] = B.[NI Number])
GROUP BY A.[Pay Number], A.[NI Number], A.[Contracted Hours]
ORDER BY 2, 3 DESC;

which produces the following results:

PayNo: NI: Hours: Rank
P00000021 NM0000011 37.5 1
P00000018 NM0000011 2 3
P00000019 NM0000011 2 3
P00000020 NM0000011 0.25 4

What I would like to see here is 1 2 3 4, is there any way to do this?

Thanks,

 
Hi Duane,
The only one I have is date started but that can be the same for some records too.

Thanks,

Tom.

 
That's your answer then.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Hi Skip,
I gave it a go and here is the result:

Pay Number NI Number Contracted Hours Date Started Rank
P00000021 NM0000011 37.5 10/10/1999 1
P00000018 NM0000011 2 01/01/2011 2
P00000019 NM0000011 2 01/10/2012 1
P00000020 NM0000011 0.25 01/10/2012 2


Here is the code I used.


SELECT A.[Pay Number], A.[NI Number], A.[Contracted Hours], A.[Date Started], Count(*) AS Rank
FROM tblAssNo1 AS A LEFT JOIN tblAssNo1 AS B ON (A.[Date Started] <= B.[Date Started]) AND (A.[NI Number] = B.[NI Number]) AND (A.[Contracted Hours]<= B.[Contracted Hours])
GROUP BY A.[Pay Number], A.[NI Number], A.[Contracted Hours], A.[Date Started]
HAVING (((A.[Date Started]) Is Not Null))
ORDER BY 2, 3 DESC;


As I said, some of the contracted hours and start dates are the same for some posts.

Thanks,

Tom.
 
Hi,
I changed "(A.[Contracted Hours]<= B.[Contracted Hours])" to this "(A.[Date Started] >= B.[Date Started])" and got better results:

Pay Number NI Number Contracted Hours Date Started Rank
P00000021 NM0000011 37.5 10/10/1999 1
P00000018 NM0000011 2 01/01/2011 2
P00000019 NM0000011 2 01/10/2012 3
P00000020 NM0000011 0.25 01/10/2012 4


But if the dates are the same too I get this:


Pay Number NI Number Contracted Hours Date Started Rank
P00000000 NM0000001 15 01/01/2011 2
P00000001 NM0000001 15 01/01/2011 2

Thanks,

Tom.
 

Hi,

This should have been

I changed this "(A.[Date Started] <= B.[Date Started]))" to this "(A.[Date Started] >= B.[Date Started])" and got better results:

Sorry for the confusion.

Tom.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top