MySQL query for opposite of GROUP BY or DISTINCT?

Question

I have the answer below that worked fine for me:

now instead of selecting distinct or group by , I want to select all that records ext_no having value zero only.

How to modify this answer? I tried using order by but it's showing an error. Any help will be appreciated. Thanks in advance.

SELECT   ext_no, MAX(value)
FROM     test
GROUP BY ext_no
HAVING   COUNT(*) > 2 AND 
         COUNT(*) = COUNT(CASE value WHEN 0 THEN 1 END)

Answer 1

There are a couple of ways you can achieve this. Using a couple of correlated subqueries in the WHERE clause or by JOINing to a table containing MAX(value) and COUNT(*) values for each value of ext_no:

Query 1

SELECT ext_no
FROM test t1
WHERE NOT EXISTS (SELECT * 
                  FROM test t2
                  WHERE t2.ext_no = t1.ext_no AND value != 0)
  AND (SELECT COUNT(*)
       FROM test t3
       WHERE t3.ext_no = t1.ext_no) > 2

The first subquery checks that this ext_no has no value other than 0. The second checks that there are more than 2 copies of this ext_no.

Query 2

In this query we apply all the conditions (no non-zero values of value and more than 2 copies of ext_no) in the JOIN condition.

SELECT t1.ext_no
FROM test t1
JOIN (SELECT ext_no, MAX(value) AS max_value, COUNT(*) AS count
      FROM test 
      GROUP BY ext_no) t2
  ON t2.ext_no = t1.ext_no AND t2.max_value = 0 AND t2.count > 2

Output (for both queries)

ext_no
12133
12133
12133
11505
11505
11505
11505

Demo on SQLFiddle