qualify rank question

[Valeria]

Hello,

I have a question concerning qualify rank. Please have a look at the following Skript

sel distinct ktnrkonzdb ,generated_artikelnummer6, puls_price/100.00

from dapo_view.V_PREIS_HISTORIE a

join abandoned_basket_noord b on a.generated_artikelnummer6 = b.artnr

where mandant_id =28 and gueltig_bis = date '9999-12-31'

group by 1 qualify rank (puls_price desc ) <2

As results I get per customer_number (ktnrkonzdb) the the more expensive article they put in the basket. But sometimes happen that two or more articles have the same price and for this reason I get two or three rows with the same customer_number, two/three different articles with the same prices:

20724714  953842 209,95
20853190  938456 34,95
20853190  938455 34,95
20883367  944310 17,95

What shall I modify in the skript in order to obtain for this cases that I markd in red only one article and not both? it could be chosen randomly.

20724714  953842 209,95
20853190  938456 34,95
20883367  944310 17,95

Thank you and best regards,

Valeria

[DaveWellman]

Hi,

I think I'd use ROW_NUMBER instead of RANK. Try something like:

sel ktnrkonzdb ,generated_artikelnummer6, puls_price/100.00

from dapo_view.V_PREIS_HISTORIE a
join abandoned_basket_noord b 
on a.generated_artikelnummer6 = b.artnr
  and mandant_id =28 
  and gueltig_bis = date '9999-12-31'

qualify row_number() over(partition by ktnrkonzdb ,generated_artikelnummer6, puls_price order by puls_price desc ) <2

As you said, if you have multiple rows with the same value for columns  ktnrkonzdb ,generated_artikelnummer6 and puls_price then you have no control over which one actually gets returned (but you won't know because their all the same anyway!).

HTH

Dave

[Valeria]

Hello,

with this sql it is worst than before, becasue I get 10.000 rows back and I see many times the same customer number.

I would like to have as result one customer number per row (and not many times the same customer number), with the article with highest price. When two articles have the same (highest) price, I want to see only one customer per row.

Is it possible?

Best regards,

Valeria

[Waldar]

Hi Valeria,

Just reduce the "partition by" part of @DaveWellman solution :

qualify row_number() over(partition by ktnrkonzdb order by puls_price desc) = 1

[Valeria]

it works!

Thank you very much :D

[DaveWellman]

Yep, @Waldar beat me to it.

Just change that clause and you should be ok. Sorry