CODEWITHSUNDEEP
mysql
Ritika
Ritika

Reputation: 79

Select all rows if COUNT(DISTINCT COLNAME)>1

I want to select ALL rows in a table, if even one value in a particular column is different from any value in the column. So to check if all the rows are different I first do a count(distinct col_name)>1. 

| NAME | YEAR_BORN | SCHOOL |  
| Jack | 2000      | A      |  
|Jill  |2001       |A       |
|Jane  |2000       |A       |

Since the year born here is different for Jill as compared to Jack and Jane, I want to display the column as is. If Jill had the same year born as them, I would not want return anything.

How do I now select all rows if this condition is satisfied?

Upvotes: 0

Views: 52

Answers (2)

forpas
forpas

Reputation: 164089

You need a subquery in the where clause:

select * from tablename
where (select count(distinct year_born) from tablename) > 1

See the demo.
Results:

| NAME | YEAR_BORN | SCHOOL |
| ---- | --------- | ------ |
| Jack | 2000      | A      |
| Jill | 2001      | A      |
| Jane | 2000      | A      |

Upvotes: 1

Kamil Waniczek
Kamil Waniczek

Reputation: 67

You can try SELECT * FROM table_name WHERE count(distinct col_name)>1

Upvotes: 0

Related Questions