CODEWITHSUNDEEP
sql
bry888
bry888

Reputation: 297

Partitioning with some condition in another column

I have data like this:

some_data, p_column, type
pla, 1, a
bla, 1, b
sth, 1, a
sth, 2, a

Than lets say I partition by p_column over some descending dates in some_data and with rank I get:

some_data, p_column, type, rank
pla, 1, a, 1
bla, 1, b, 2
sth, 1, a, 3
sth, 2, a, 1

For each partition I want to select row with the lowest rank where the type is b, else if there is no row with type b in the partition I want to select row with the lowest rank with type a.

So I will get rows (for the example):

some_data, p_column, type, rank
bla, 1, b, 2
sth, 2, a, 1

If this is not clear, I attach pseudocode:

for each partition:
    if type b is in partition:
        select row b with lowest rank
    else:
        select row a with lowest rank

Upvotes: 1

Views: 1150

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

One conceptual way of approaching this is to realize that you really want a double partition here, one layer for the p_column and a second layer for the type. Based on your precedence rules, you want to give the b type a higher priority than a. So we can partition on the p_column followed by the type, and then use row number again to choose the lowest record for each partition.

SELECT some_data, p_column, type
FROM
(
    SELECT some_data, p_column, type,
        ROW_NUMBER() OVER (PARTITION BY p_column
                           ORDER BY CASE WHEN type = 'b' THEN 0 ELSE 1 END) rn
    FROM
    (
        SELECT some_data, p_column, type,
            RANK() OVER (PARTITION BY p_column, type ORDER BY some_date_col) rank
        FROM yourTable
    ) t
    WHERE t.rank = 1
) t
WHERE t.rn = 1
ORDER BY p_column;

enter image description here

Demo

Upvotes: 3

Related Questions