Rank and partition query in SQL

Question

I have a table in AS400 as below

Type    Values  Status
A   1   Y
A   2   N
A   3   Y
A   4   Y
A   5   N
B   2   Y
B   7   N
C   3   Y
C   5   N
C   4   Y
C   6   Y
C   7   Y
C   1   Y
D   3   Y
D   5   Y
E   7   N
E   4   N
E   3   Y
E   6   N
E   7   Y
E   8   N

What I need is Top 2 of each type that have a status Y. I.e. the result should be something like A 1 , A 3, B 2 , C3, C4, D3, D5, E3, E7.

The query I have used is this

SELECT type,
    REFERENCES
FROM (
    SELECT type,
        REFERENCES,
        STATUS,
        rank() OVER (PARTITION BY Type ORDER BY REFERENCES DESC) AS Rank
    FROM Tables
    ) a
WHERE rank <= 2
    AND Type IN (A,B,C,D,E)
    AND STATUS = Y;

The issue here is it doesn't filter out the status beforehand. It picks up the top 2, then filters out with Y. So the result looks something like A1 instead of A1 and A3, because it has first picked A1 and A2 , and then filtered out A2 . Where do I insert the Status=y to get a more accurate result. I am a novice in SQL so if theres a better way to write the above query as well, I am fine with that.

Stick your `status = Y` bit of your WHERE clause into the subquery. It would make sense, given your data, to stick the `Type IN (A,B,C,D,E)` portion into your subquery as well. — JNevill, May 22 '17 at 16:44
@JNevill - Commonsense but worked absolutely perfectly. Cheers! — user8049037, May 23 '17 at 16:27

score 0 · Answer 1 · answered May 22 '17 at 16:47

This outta do it. Use the with clause to feed the filtered result into a new query which then you can rank.

with testtable (type, value, status) as (
select 'A', 1, 'Y' from dual union all
select 'A', 2, 'N' from dual union all
select 'A', 3, 'Y' from dual union all
select 'A', 4, 'Y' from dual union all
select 'A', 5, 'N' from dual union all
select 'B', 2, 'Y' from dual union all
select 'B', 7, 'N' from dual union all
select 'C', 3, 'Y' from dual union all
select 'C', 5, 'N' from dual union all
select 'C', 4, 'Y' from dual union all
select 'C', 6, 'Y' from dual union all
select 'C', 7, 'Y' from dual union all
select 'C', 1, 'Y' from dual union all
select 'D', 3, 'Y' from dual union all
select 'D', 5, 'Y' from dual union all
select 'E', 7, 'N' from dual union all
select 'E', 4, 'N' from dual union all
select 'E', 3, 'Y' from dual union all
select 'E', 6, 'N' from dual union all
select 'E', 7, 'Y' from dual union all
select 'E', 8, 'N' from dual
)
, ys as (
select
*
from testtable
where STATUS = 'Y'
)
, yrank as (
select
type, 
value,
status,
rank() over(partition by type order by value) Y_RANK
from ys
)
select
*
from yrank
where Y_RANK <= 2

Rank and partition query in SQL

1 Answers1