INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

question about RANKING records in a table with repeated values

question about RANKING records in a table with repeated values

(OP)
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 ...

CODE

Val                             Rank   Val
2                                1      2
4                                2      4
5                                3      5 
9                                4      9
10                               5     10 

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

CODE

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?

CODE

Val                             Rank   Val
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

RE: question about RANKING records in a table with repeated values

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: question about RANKING records in a table with repeated values

(OP)
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...

CODE

tbl_List_With_Repeats                            Query Output

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.

RE: question about RANKING records in a table with repeated values

How about

CODE --> SQL

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; 

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 

Duane
Hook'D on Access
MS Access MVP

RE: question about RANKING records in a table with repeated values

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: question about RANKING records in a table with repeated values

(OP)
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)

CODE

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; 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close