Sql comparaison nested on one column

Question

Imagine a table :

 ID  Month     Year  Value 1 
  1  May        17   58      
  2  June       09   42      
  3  December   18   58      
  4  December   18   58     
  5  September  10   84      
  6  May        17   42      
  7  January    16   3

I want to return all the data that shares the same month and year where Value 1 is different. So in our example, I want to return 1 and 6 only but not 3 and 4 or any of the other entries.

Is there a way to do this? I am thinking about a combination of distinct and group by but can't seem to come up with the right answer being new to SQL.

Thanks.

You are looking for something like this : https://stackoverflow.com/questions/7151401/sql-query-for-finding-records-where-count-1 — z atef, Jun 29 '17 at 19:58

score 1 · Answer 1 · answered Jun 29 '17 at 20:05

For each row you can examine aggregates in its group with the OVER clause. eg:

create table #t(id int, month varchar(20), year int, value int)

insert into #t(id,month,year,value)
values 
 (1,'May'      ,17,  58      ),
 (2,'June'     ,09,  42      ),
 (3,'December' ,18,  58      ),
 (4,'December' ,18,  58      ),
 (5,'September',10,  84      ),
 (6,'May'      ,17,  42      ),
 (7,'January'  ,16,  3       );


 with q as
 (
     select *, 
            min(value) over (partition by month,year) value_min,
            max(value) over (partition by month,year) value_max
     from #t
)
select id,month,year,value
from q
where value_min <> value_max;

Shad · Accepted Answer · 2017-06-29T20:31:57.597

1

It could be done without grouping, but with simple self-join:

    select distinct t1.*
    from [Table] t1
    inner join [Table] t2 on
        t1.Month = t2.Month
        and t1.Year = t2.Year
        and t1.Value_1 <> t2.Value_1

You can find some information and self-join examples here and here.

edited Jun 29 '17 at 20:31

answered Jun 29 '17 at 20:19

Shad

4,423
3
36
37

The self join does work and is the simplest solution. Thank you. – Niehm Jun 30 '17 at 12:42

score 0 · Answer 3 · answered Jun 29 '17 at 20:00

0

If I understood your question correctly, you are looking for the HAVING keyword.

If you GROUP BY Month, Year, Value_1 HAVING COUNT(*) = 1, you get all combinations of Month, Year and Value_1 that have no other occurrence.

answered Jun 29 '17 at 20:00

CryingSofa

563
1
5
5

Sql comparaison nested on one column

3 Answers3