CODEWITHSUNDEEP
mysql
Mujeeb
Mujeeb

Reputation: 1225

How to select rows in which data in a certain column is never repeated-SQL

I have a table with just 4 rows. 

FIRST  LAST    AGE
luke   david   42
jester prince  32
luke   mike    16
sean   paul    22

I want to select only those rows in which the first name is never repeated. So the result I need is 

FIRST  LAST     AGE
jester prince   32
sean   paul     22

The sql query should ignore all the rows in which the first name is being repeated. The simple distinct query doesn't work here because it just removes multiple entries. I want a query which can remove ALL the rows of the repeating first name.

Upvotes: 0

Views: 814

Answers (4)

xkeshav
xkeshav

Reputation: 54022

TRY

SELECT * FROM `tbl_name` GROUP BY(`name`) HAVING COUNT(`name`) = 1

Upvotes: 1

Ruddy Lodonou Oke
Ruddy Lodonou Oke

Reputation: 221

select FIRST, LAST, AGE 

from MY_TABLE 

group by FIRST 
HAVING count(FIRST) = 1

Upvotes: 0

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

You may use a NOT IN clause, like this:

SELECT * FROM table1
WHERE first NOT IN (
  SELECT first FROM table1
  GROUP BY first
  HAVING COUNT(*) > 1
)

Inner query finds all first names which are repeated and then using the NOT IN clause those duplicate first names are removed from the final result.

Upvotes: 0

Naved Munshi
Naved Munshi

Reputation: 507

You can use group by if distinct does not work

   SELECT * FROM Table GROUP BY FIRST

Upvotes: 0

Related Questions