If I have a table in SQL as:

What I need to do, logically is:

1. Get the top row of each group when grouped by id, ordered by code
2. Create a new column to show if the code column contained a 7 anywhere, within the group

Desired result:

I think it needs a "CASE WHEN" statement in the SELECT, but have not worked it out. What query can I execute to get this?

CodePudding user response：

Seems like you can use a MIN and a conditional aggregate:

SELECT id,
       MIN(Code) AS Code,
       CASE WHEN COUNT(CASE code WHEN 7 THEN 1 END) > 0 THEN 'Y' ELSE 'N' END AS C7
FROM dbo.YourTable
GROUP BY id;

CodePudding user response：

There is probably a better way to do this, but what comes to mind is that first you have to partition the table to get the top one based on whatever that criteria is, then join back against itself to find the ones with the 7

declare @table1 table (id int not null, code int not null)

insert into @table1 (id, code)
values
(1,6),
(1,8),
(1,4),
(2,3),
(2,7),
(2,4),
(3,7),
(3,6),
(3,7)

select id, code, c7
from (
    select t.id ,t.code
        ,(CASE WHEN c.id is null then 'N' else 'Y' END) as c7
        ,ROW_NUMBER() OVER (PARTITION BY t.id order by t.code) AS p 
    from @table1 t
        left outer join (
            select id, code, 'Y' as c7
            from @table1
            where code = 7) c on c.id = t.id
    ) sorted
where sorted.p = 1