Microsoft Access - Another Ranking Question - Asked By Ben on 04-Jan-11 05:00 PM

Hello, I have the following table
ID  Store#  Genre  WOS
1  1000    Action  2  
2  1001    Action  4
3  1002    Action  4
4  1003    Action  1  
5  1000    RPG     3
6  1001    RPG     1

I would like to group the Genre and rank based off WOS, I have it ranking nicely but my problem is ties. For example the following code assigns the same rank for records within the same genre that have the same WOS the same rank, when I need it to rank in sequence. I think the ID column can break the tie, but not sure how/where to put that. My code is below...

SELECT [Master - Store - Genre].ID, [Master - Store - Genre].[STORE#], [Master - Store - Genre].GENRE, [Master - Store - Genre].[Actual WoS], (SELECT COUNT (*)  FROM [Master - Store - Genre] AS [XX]
WHERE [Master - Store - Genre].Genre = [XX].Genre AND [Master - Store - Genre].[Actual WoS] > [XX].[Actual WoS] ORDER BY [Master - Store - Genre].[ID])+1 AS Rank
FROM [Master - Store - Genre]
ORDER BY [Master - Store - Genre].GENRE, [Master - Store - Genre].[Actual WoS];


Any help would great!

Thanks

Need more info - Lalitha Kumaran replied to Ben on 05-Jan-11 02:25 AM

can u please clearly mention ur requirement.

correct me if am wrong.

Ranking based on which  store,genre (or) genre,wos columns?..

solution #1 - Lalitha Kumaran replied to Ben on 05-Jan-11 02:30 AM

Hope this is what u expected

select id,store,genre,wos,
dense_rank() over(partition by genre order by store desc) from
dummytbl




solution #1 - Lalitha Kumaran replied to Ben on 05-Jan-11 02:35 AM

select id,store,genre,wos,
dense_rank() over(partition by genre order by store desc) ranks from
dummytbl

http://eggheadcafe.com/FileUpload/5206786_rank.JPG  


Ben replied to Lalitha Kumaran on 05-Jan-11 10:19 AM
I really didn't follow this, but I tried the following:
SELECT ID, [Store#],Genre,[Actual WOS],Dense_Rank() over (Partition by Genre order by [Actual WoS]) ranks from [Master - Store - Genre];

[Master - Store - Genre] is the actual name of the table. I did recieve the error "Syntax error (missing operator) in query expression 'Dense_Rank() over (Partition by Genre order by [Actual WoS]) ranks'.

Again this in access and looking for a way to correct the way the "ties" are handled.

Thanks
Here's the query to avoid ties - Lalitha Kumaran replied to Ben on 06-Jan-11 12:26 AM

try the below query which may solve your problem

select id,store,genre,wos,
dense_rank() over(partition by genre order by wos,store) ranks
from dummytbl

Result
******
id   store  genre    wos  ranks   

4 1003    Action 1    1
1 1000    Action 2    2
2 1001    Action 4    3
3 1002    Action 4      4
6 1001    Rpg      1   1
5 1000    Rpg      3   2

Ben replied to Lalitha Kumaran on 10-Jan-11 10:41 AM
Received same error as before "Syntax error...." but I did find the solution to my problem.
Replace this:
(SELECT COUNT (*)  FROM [Master - Store - Genre] AS [XX]
WHERE [Master - Store - Genre].Genre = [XX].Genre AND [Master - Store - Genre].[Actual WoS] > [XX].[Actual WoS] ORDER BY [Master - Store - Genre].[ID])+1 AS Rank
with this:
(SELECT COUNT (*)  FROM [Master - Store - Genre] AS [XX]
WHERE [Master - Store - Genre].Genre = [XX].Genre AND ([Master - Store - Genre].[Actual WoS] > [XX].[Actual WoS] OR ([Master - Store - Genre].[Actual WoS]=[XX].[Actual WoS] AND [Master - Store - Genre].[ID]>[XX].[ID])))+1 AS Rank

Hope this helps someone.