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,
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,