CODEWITHSUNDEEP
sqlsql-server
asdfok
asdfok

Reputation: 15

How to specify Where clause for each group from group by

I need to group all data by column "Ico" and from each group (125, 130, 140) select only those, where column "ValidTo" is not NULL.

I have a set of data:

Id  Ico  ValidTo
1   125   NULL
2   125   7.5.2019
3   125   7.5.2019
4   130   20.5.2019
5   130   21.5.2019
6   140   NULL
7   140   NULL

So the result would be

Ico    ValidTo
130   21.5.2019

The result doesn´t have to be grouped and it doesn´t need to show only the maximum valid date (but it would be nice)

Perfmance is not an issue, the query is just for testing purposes to test if my several LINQ queries does the job right.

Any suggestions on how to achieve this? Thank you.

Upvotes: 0

Views: 486

Answers (2)

Qirel
Qirel

Reputation: 26460

One way of going about it is grouping by the ico column, and checking if the count of rows in that group is equal to the sum of non-null rows.

SELECT ico, MAX(validto)
FROM foo t1
GROUP BY ico
HAVING SUM(CASE WHEN validto IS NULL THEN 0 ELSE 1 END) = COUNT(id)

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

Just use not exists

 select * from your_data t1
 where not exists (
    select 1
    from your_data t2
    where t1.ico = t2.ico and t2.validto is null
 )

This solution is not doing the group of the data. If you are really interested in the greatest per group you may use row_number() window functions

select *
from (
     select *,
            row_number() over (partition by t1.ico order by validto desc) rn
     from your_data t1
     where not exists (
        select 1
        from your_data t2
        where t1.ico = t2.ico and t2.validto is null
     )
) t
where t.rn = 1

Upvotes: 1

Related Questions