Rank query returning same rank (1 Viewer)

FoolzRailer

New member
Local time
Today, 19:25
Joined
Apr 15, 2016
Messages
25
Hello

I'm trying to Rank my column SKU by lowest price.

I have set up the query as described below. However for two items with the same price, it returns the wrong rank.

Example where two items have the same price, it should be ranked 1, 2 (don't care which one of the two is first). But instead it returns two 2's or other duplicate values. How can I go about getting ranks correct for same priced items?

Any help would be much appreciated!




Code:
SELECT PricerunnerUnionMedID.SKU AS SKU, PricerunnerUnionMedID.x_firma AS x_firma, PricerunnerUnionMedID.x_pris AS x_pris, Count(PricerunnerUnionMedID_1.SKU) AS Rang, PricerunnerUnionMedID.PricerunnerID AS PricerunnerID, PricerunnerUnionMedID.FirmaName AS FirmaName, First(PricerunnerUnionMedID.FirmanavnPris) AS FirmanavnPris
FROM PricerunnerUnionMedID AS PricerunnerUnionMedID_1 INNER JOIN PricerunnerUnionMedID ON PricerunnerUnionMedID_1.SKU = PricerunnerUnionMedID.SKU
WHERE (((PricerunnerUnionMedID_1.x_pris)<=[PricerunnerUnionMedID].[x_pris]))
GROUP BY PricerunnerUnionMedID.SKU, PricerunnerUnionMedID.x_firma, PricerunnerUnionMedID.x_pris, PricerunnerUnionMedID.PricerunnerID, PricerunnerUnionMedID.FirmaName;
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:25
Joined
Oct 29, 2018
Messages
21,358
Hi. My guess is you can only do it that way using VBA.
 

vba_php

Forum Troll
Local time
Today, 13:25
Joined
Oct 6, 2019
Messages
2,884
Hi. My guess is you can only do it that way using VBA.
i agree with that. do you want someone to give you a sample that you can use, foolz?
 

plog

Banishment Pending
Local time
Today, 13:25
Joined
May 11, 2011
Messages
11,611
Example where two items have the same price, it should be ranked 1, 2 (don't care which one of the two is first)

That's not how computers work. They apply an algorithm. If that algorithm isn't well defined its not going to be able to make decisions by itself.

You need to better define how items with the same cost are ranked. Perhaps bring in another field to break ties--rank items by price and then by their name alphabetically.
 

vba_php

Forum Troll
Local time
Today, 13:25
Joined
Oct 6, 2019
Messages
2,884
They apply an algorithm.
algorithms will be the downfall of society. one of my goals is to keep my friends and family as far away from those stupid things as I can. and they agree with me. keep it human. business intelligence is fine, but there will be many people that get sucked into that trap of letting algos run their lives.
 

FoolzRailer

New member
Local time
Today, 19:25
Joined
Apr 15, 2016
Messages
25
Thank you for all the replies.



Was hoping it was possible to do this with a perhaps a subquery or some Iifs, but seems VBA is the way to go. If anyone has a sample code that I could use, that would be much appreciated :)


@Plog. I do have for each item the "Vendorname"-"Price" which I think could be used to differentiate rank based on which one is first alphabetically. Just doesn't seem like I can use that idea in the way i hoped.
 

vba_php

Forum Troll
Local time
Today, 13:25
Joined
Oct 6, 2019
Messages
2,884
foolz,

i don't know about the other guys, but if I were to write a sample for you i would have to look at your db so i could include it in there. upload it if you want me to help out. if not, ask one of the others.
 

isladogs

MVP / VIP
Local time
Today, 18:25
Joined
Jan 14, 2017
Messages
18,186
Have a look at my article on rank orders in queries.
http://www.mendipdatasystems.co.uk/rank-order-queries/4594424063

This uses the Serialize function instead of subqueries and in my opinion gives better performance.
Although tied values have the same rank by default, the article also shows how you can assign a unique rank for each record including tied values
 

Users who are viewing this thread

Top Bottom