Geek Answers Handbook

SQL filtering using COALESCE or window function

I have a table with the following simplified data:

PK FK Type ---------------- 1 11 A 2 11 B 3 12 B 4 13 C 5 13 D 6 14 D

And I need a result set:

 PK FK Type --------------- 1 11 A 3 12 B 4 13 C 6 14 D

So, if the given value of FK is of type A, don't give me any lines with B, C or D. Or, if the value is of type B, then filter C and D, etc. It looks like I need to apply a window function or combine it, but I'm not sure how to do it.

+4
source share
2 answers

Using:

 SELECT x.* FROM (SELECT s.*, ROW_NUMBER() OVER(PARTITION BY s.fk ORDER BY s.pk) rnk FROM YOUR_TABLE s) x WHERE x.rnk = 1

... or using CTE (no difference in performance):

 WITH example AS ( SELECT s.*, ROW_NUMBER() OVER(PARTITION BY s.fk ORDER BY s.pk) rnk FROM sample s) SELECT x.* FROM example x WHERE x.rnk = 1

Evidence:

 WITH sample AS ( SELECT 1 AS PK, 11 AS FK, 'A' AS [TYPE] UNION ALL SELECT 2 AS PK, 11 AS FK, 'B' AS [TYPE] UNION ALL SELECT 3 AS PK, 12 AS FK, 'B' AS [TYPE] UNION ALL SELECT 4 AS PK, 13 AS FK, 'C' AS [TYPE] UNION ALL SELECT 5 AS PK, 13 AS FK, 'D' AS [TYPE] UNION ALL SELECT 6 AS PK, 14 AS FK, 'D' AS [TYPE]) SELECT x.* FROM (SELECT s.*, ROW_NUMBER() OVER(PARTITION BY s.fk ORDER BY s.pk) rnk FROM sample s) x WHERE x.rnk = 1

Result:

 pk fk type ---------------- 1 11 A 3 12 B 4 13 C 6 14 D
+4
source

So, for each FK value, do you want the minimum PK value to match, and the corresponding type for PK? As always, do it step by step.

What are the required PK values?

 SELECT FK, MIN(PK) AS PK FROM SimplifiedData GROUP BY FK

How to get the corresponding rows - why, connect to the main table, of course:

 SELECT S.PK, S.FK, S.Type FROM SimplifiedData AS S JOIN (SELECT FK, MIN(PK) AS PK FROM SimplifiedData GROUP BY FK ) AS T ON S.PK = T.PK ORDER BY S.FK;
+1
source

More articles:


All Articles