Requirement: Table data:

Required data:

Can someone help me which method I should use here to filter records.

I am grouping this data depends upon multiple fields. After grouping I need to take single row for Col B.

Eg. Col B = 123 has only 1 record so we need to fetch that. And for col B = 456, we have 2 records so in that case we just need to take records which is col A = Y

Thanks in advance

I have tried analytical function here but it's not working as expected

CodePudding user response：

This answer assumes that your logic is to return the yes flag in cases where a B group have 2 records. We can use ROW_NUMBER here:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY ColB ORDER BY ColA DESC) rn
    FROM yourTable t
)

SELECT ColA, ColB
FROM cte
WHERE rn = 1;

CodePudding user response：

we just need to take records which is col A = Y

If you want to take records (plural) for each col_b then use the RANK or DENSE_RANK analytic functions:

SELECT ColA,
       ColB
FROM   (
  SELECT t.*,
         RANK() OVER (PARTITION BY ColB ORDER BY ColA DESC) AS rnk
  FROM   table_name t
)
WHERE rnk = 1;

After grouping I need to take single row for Col B.

If you only want a single row per col_b then use the ROW_NUMBER analytic function (and if the priority cannot be sorted alphabetically then you can use a CASE expression):

SELECT ColA,
       ColB
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (
           PARTITION BY ColB
           ORDER     BY CASE ColA WHEN 'Y' THEN 0 ELSE 1 END
         ) AS rn
  FROM   table_name t
)
WHERE rn = 1;