How to select distinct records based on a given condition?

user_11317272 0
Viewed 29

I have the following table in the MySQL database:

| id | col | val |
| -- | --- | --- |
| 1  | 1   | y   |
| 2  | 1   | y   |
| 3  | 1   | y   |
| 4  | 1   | n   |
| 5  | 2   | n   |
| 6  | 3   | n   |
| 7  | 3   | n   |
| 8  | 4   | y   |
| 9  | 5   | y   |
| 10 | 5   | y   |

Now I want to distinctly select the records where all the values of similar col are equal to y. I tried both the following queries:

SELECT DISTINCT `col` FROM `tbl` WHERE `val` = 'y'

SELECT `col` FROM `tbl` GROUP BY `col` HAVING (`val` = 'y')

But it's not working out as per my expectation. I want the result to look like this:

| col |
| --- |
| 4   |
| 5   |

But 1 is also being included in the results with my queries. Can anybody help me building the correct query? As far as I understand, I may need to create a derived table, but can't quite figure out the right path.

sql
2 Answers

You are close, with the second query. Instead, compare the min and max values:

SELECT `col`
FROM `tbl`
GROUP BY `col`
HAVING MIN(val) = MAX(val) AND MIN(`val`) = 'y';
user_11440350

Check that 'y' is the minimum value:

HAVING MIN(val) = 'y'
user_37060160
Related