SQL rank starting with 2

Question

select 
    TRANSACTION_DT,
    SUBSCRIPTION_ID,
    KEY_ID,
    EMAIL,
    PURCHASE_PRODUCT_ID, 
    rank () over (partition by SUBSCRIPTION_ID,KEY_ID order by TRANSACTION_DT desc) as rnk 
from "DC"."BW_BOOKINGS"
where email='abc@gmail.com'

The above SQL statement result table looks like this

TRANSACTION_DT	SUBSCRIPTION_ID	KEY_ID	EMAIL	PURCHASE_PRODUCT_ID	RNK
2021-07-14 09:42:47.710 -0700	S107283	122693	abc@gmail.com	143510	1
2021-07-14 09:42:47.710 -0700	S107283	122693	abc@gmail.com	139724	1
2020-07-14 09:22:14.033 -0700	S107283	122693	abc@gmail.com	143510	3
2020-07-14 09:22:14.033 -0700	S107283	122693	abc@gmail.com	139724	3

But when I change the SQL statement to this

select * from (
    select 
        TRANSACTION_DT,
        SUBSCRIPTION_ID,
        KEY_ID, 
        EMAIL,
        PURCHASE_PRODUCT_ID, 
        rank () over (partition by SUBSCRIPTION_ID,KEY_ID order by TRANSACTION_DT desc) as rnk 
    from "DC"."BW_BOOKINGS"
) t
where email='abc@gmail.com'

My table looks like this:

TRANSACTION_DT	SUBSCRIPTION_ID	KEY_ID	EMAIL	PURCHASE_PRODUCT_ID	RNK
2021-07-14 09:42:47.710 -0700	S107283	122693	abc@gmail.com	143510	3
2021-07-14 09:42:47.710 -0700	S107283	122693	abc@gmail.com	139724	3
2020-07-14 09:22:14.033 -0700	S107283	122693	abc@gmail.com	139724	5
2020-07-14 09:22:14.033 -0700	S107283	122693	abc@gmail.com	143510	5

I want to get results from the table only when rnk=1, but the rank in table 2 starts with 3 which is not helping me to filter out results on where rnk=1. Also, Can anyone tell me why the order of PURCHASE_PRODUCT_ID chases in row 3.

user_43992 · Answer

The second solution is ranking ALL rows, and then you throw away rows not match the email address, thus you have already "lost 1"

The filter need to happen before the ranking function, not after it.

So if you want only RANK = 1 then you can qualify:

select 
    TRANSACTION_DT,
    SUBSCRIPTION_ID,
    KEY_ID, 
    EMAIL,
    PURCHASE_PRODUCT_ID, 
    rank () over (partition by SUBSCRIPTION_ID,KEY_ID order by TRANSACTION_DT desc) as rnk 
from "DC"."BW_BOOKINGS"
qualify rnk  = 1

if you want only the RANK 1 rows for email X then do the filtering in the WHERE, then QUALIFY to keep the wanted rows:

select 
    TRANSACTION_DT,
    SUBSCRIPTION_ID,
    KEY_ID, 
    EMAIL,
    PURCHASE_PRODUCT_ID, 
    rank () over (partition by SUBSCRIPTION_ID,KEY_ID order by TRANSACTION_DT desc) as rnk 
from "DC"."BW_BOOKINGS"
where email='abc@gmail.com'
qualify rnk  = 1

Greg's note, "if you don't want the rank you don't need it" looks like:

select 
    TRANSACTION_DT,
    SUBSCRIPTION_ID,
    KEY_ID, 
    EMAIL,
    PURCHASE_PRODUCT_ID
from "DC"."BW_BOOKINGS"
where email='abc@gmail.com'
qualify rank () over (partition by SUBSCRIPTION_ID,KEY_ID order by TRANSACTION_DT desc) = 1

SQL rank starting with 2

1 Answers