CODEWITHSUNDEEP
sqlpostgresql
Mjoy
Mjoy

Reputation: 1

SQL Query to apply a command to multiple rows

I am new to SQL and trying to write a statement similar to a 'for loop' in other languages and am stuck. I want to filter out rows of the table where for all of attribute 1, attribute2=attribute3 without using functions.

For example:

    | Year | Month | Day|
    | 1  | 1    | 1 |
    | 1  | 2    | 2 |
    | 1  | 4    | 4 |
    | 2  | 3    | 4 |
    | 2  | 3    | 3 |
    | 2  | 4    | 4 |
    | 3  | 4    | 4 |
    | 3  | 4    | 4 |
    | 3  | 4    | 4 |

I would only want the row

    | Year | Month | Day|
    |:---- |:------:| -----:|
    | 3  | 4    | 4 |

because it is the only where month and day are equal for all of the values of year they share.

So far I have select year, month, day from dates where month=day but unsure how to apply the constraint for all of year

Upvotes: 0

Views: 92

Answers (2)

Marth
Marth

Reputation: 24812

-- month/day need to appear in aggregate functions (since they are not in the GROUP BY clause),
-- but the HAVING clause ensure we only have 1 month/day value (per year) here, so MIN/AVG/SUM/... would all work too
SELECT year, MAX(month), MAX(day)
FROM my_table
GROUP BY year
HAVING COUNT(DISTINCT (month, day)) = 1;
year max max
3 4 4

View on DB Fiddle

Upvotes: 1

Stu
Stu

Reputation: 32619

So one way would be

select distinct [year], [month], [day]
from [Table] t
where [month]=[day]
and not exists (
  select * from [Table] x
  where t.[year]=x.[year] and t.[month] <> x.[month] and t.[day] <> x.[day]
)

And another way would be

select distinct [year], [month], [day] from (
    select *,
        Lead([month],1) over(partition by [year] order by [month])m2,
        Lead([day],1) over(partition by [year] order by [day])d2
    from [table]
)x
where [month]=m2 and [day]=d2

Upvotes: 0

Related Questions