CODEWITHSUNDEEP
sql
moons
moons

Reputation: 209

SQL aggregate and filter functions

Consider following table:

Number | Value
1        a
1        b
1        a
2        a
2        a
3        c
4        a
5        d
5        a

I want to choose every row, where the value for one number is the same, so my result should be:

Number | Value
2        a
3        c
4        a

I manage to get the right numbers by using nested SQL-Statements like below. I am wondering if there is a simpler solution for my problem.

SELECT 
a.n,
COUNT(n)
FROM 
(
SELECT number n , value k 
FROM testtable
GROUP BY number, value
) a
GROUP BY n
HAVING COUNT(n) = 1

Upvotes: 1

Views: 108

Answers (3)

Ben Thurley
Ben Thurley

Reputation: 7161

Another alternative using exists.

select distinct num, val from testtable a
where not exists (
  select 1 from testtable b
  where a.num = b.num
  and a.val <> b.val
)

http://sqlfiddle.com/#!9/dd080dd/5

Upvotes: 2

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

You can try this

SELECT NUMBER,MAX(VALUE) AS VALUE FROM TESTTABLE
GROUP BY NUMBER
HAVING MAX(VALUE)=MIN(VALUE)

Upvotes: 5

Leszek Mazur
Leszek Mazur

Reputation: 2531

You can try also this:

SELECT DISTINCT t.number, t.value
FROM testtable t
LEFT JOIN testtable t_other
  ON t.number = t_other.number AND t.value <> t_other.value
WHERE t_other.number IS NULL

Upvotes: 3

Related Questions