question about RANKING records in a table with repeated values 1

[teach314]

i have a question about RANKING records. First, a bit of setup:

Given table: tbl_List_No_Repeats (see below left), I can add a RANK column (see below right) by ...

[b][u]Val[/u]                             [u]Rank   Val[/u][/b]
2                                1      2
4                                2      4
5                                3      5 
9                                4      9
10                               5     10

...using either query qA or query qB (see below)

qA  (qA uses a SUBQUERY in the SELECT statement)               qB  (uses a INNER JOIN in a TOTALS query)
==                                                             ==
SELECT                                                         SELECT
       (SELECT                                           	      COUNT(*) AS Rank, x.Val
		COUNT(*)                                       FROM
	FROM                                             	      tbl_List_No_Repeats AS x 
		tbl_List_No_Repeats y                          INNER JOIN 
	WHERE                                            	      tbl_List_No_Repeats AS y 
		x.Val + 1 > y.Val                              ON
	) AS Rank,                                                    x.Val >= y.Val
	x.Val	                                               GROUP BY
FROM                                                     	      x.Val
	tbl_List_No_Repeats x                                  ORDER BY 
ORDER BY                                                 	      2;

	2;

*** For very large tables, qA is SLOW, but qB is quite FAST.


Now to my question. I want to add a RANK column to a table that has REPEATED values, and I need it to be FAST, so I'm hoping to see code that is like qB above (using an INNER JOIN in a TOTALS query).

Consider table: tbl_List_with_Repeats (see below left), I can use the SLOW qA to add a RANK column (see below right) by changing only the source table name. But, is there a fast way to base the code on an INNER JOIN in a TOTALS query (like qB) when the table has repeated values?

[b][u]Val[/u]                             [u]Rank   Val[/u][/b]
2                                1      2
4                                2      4
4                                2      4 
9                                4      9
10                               5     10
10                               5     10
10                               5     10
22                               8     22

thank you in advance for any help

 

[SkipVought]

Hi,

A table is an unordered relation. Your aggregation is appropriate for a query but not for storing in a table. Standard table best practices.

Why do you think that you need this in your unordered table?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[teach314]

hi

That was my fault for trying to keep things simple by showing part of a larger problem. Let's say the table: tbl_List_With_Repeats was changed to...

[b][u]tbl_List_With_Repeats[/u][/b]                            [b][u]Query Output[/u][/b]

Empl_ID    Salary                                Empl_ID    Salary     Rank
-----------------                                --------------------------
1366       56000                                 8020       43000        1
1463       46000                                 1463       46000        2 
1899       56000                                 2899       46000        2
2574       52800                                 2574       52800        4
2899       46000                                 1366       56000        5
7988       56000                                 1899       56000        5
8020       43000                                 7988       56000        5
9999       85000                                 9999       85000        8

My main point earlier was that when I use a query like qA above to add the Rank, things get REALLY SLOW when the table is large. I was hoping that RANK could be added using a faster method (say, involving INNER JOINS in a Totals query, like qB above.)

Thanks for any hints.

 

[dhookom]

How about

SELECT X.Empl_ID, X.Salary, Count(Y.Empl_ID) + 1 AS CountOfEmpl_ID 
FROM tbl_List_With_Repeats X LEFT JOIN tbl_List_With_Repeats AS Y ON X.Salary >Y.Salary
GROUP BY X.Empl_ID, X.Salary
ORDER BY X.Salary;

[pre]
Empl_ID Salary CountOfEmpl_ID
8020 $43,000.00 1
1463 $46,000.00 2
2899 $46,000.00 2
2574 $52,800.00 4
1366 $56,000.00 5
1899 $56,000.00 5
7988 $56,000.00 5
9999 $85,000.00 8[/pre]

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Query to Return a distinct list of Salaries, rank or return row, then join to your table on Salary to add rank.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[teach314]

thanks to both

Duane - works perfectly! I noticed that with a few modifications, I could also output 'generous' ranking. (1,2,2,3,4,4,4,5)

SELECT
	x.Empl_ID, 
	x.Salary,
	COUNT(z.Salary) + 1 AS CountOfEmpl_ID
FROM
	tbl_List_With_Repeats x
LEFT JOIN
    (SELECT DISTINCT
		y.Salary
	FROM
		tbl_List_With_Repeats y
	) As z
ON
	x.Salary > z.Salary
GROUP BY
	x.Empl_, x.Salary
ORDER BY
	x.Salary;