Let's say I have this data in my table A:

I want to create a query which return the A row if exists (without the type column), else return a row with active false.

For this specific table the result will be:

How can I do this ?

I'm assuming I have to use a GROUP BY but I can't find a way to do it.

Thank you

CodePudding user response：

This is a classic row_number problem, generate a row number based on your ordering criteria, then select just the first row in each grouping.

declare @MyTable table (group_id int, [type] char(1), active bit);

insert into @MyTable (group_id, [type], active)
values
(1, 'A', 1),
(1, 'B', 0),
(1, 'C', 1),
(2, null, 0),
(3, 'B', 1),
(3, 'C', 0);

with cte as (
    select *
      , row_number() over (
          partition by group_id
          order by case when [type] = 'A' then 1 else 0 end desc, active asc
      ) rn
    from @MyTable
)
select group_id, active
from cte
where rn = 1
order by group_id;

Returns:

Note: Providing the DDL DML as I have shown makes it much easier for people to assist.

CodePudding user response：

This should do it. We select all the distinct group_ids and then join our table back to that. There is an ISNULL function that will insert the 'false' when 'A' type records are not found.

DECLARE @tableA TABLE (
    group_id int
    , [type] nchar(1)
    , active nvarchar(10)
);

INSERT INTO @tableA (group_id, [type], active)
VALUES
    (1, 'A', 'true')
    , (1,'B','false')
    , (1,'C', 'false')
    , (2, null, 'false')
    , (3, 'B', 'true')
    , (3, 'C', 'false')
;

SELECT
    gid.group_id
    , ISNULL(a.active,'false') as active
FROM (SELECT DISTINCT group_id FROM @tableA) as gid
    LEFT OUTER JOIN @tableA as a
        ON a.group_id = gid.group_id
        AND a.type = 'A'