Malay Dave Malay Dave - 21 days ago 5
MySQL Question

How to display Unique record number using sql?

I have student table


id | name | zip
1 | abc | 1234
2 | xyz | 4321
3 | cde | 1234



And i want to only display unique zip code which is 4321. I don't want to display the 1 and 3 record number. So, how can i display the unique record only?

Thanks In Advance.

Answer

The following query will give you all zip codes which don't appear in duplicate:

SELECT zip
FROM yourTable
GROUP BY zip
HAVING COUNT(*) = 1

If you want to also get the full records then you can use the above as a subquery to filter the original table:

SELECT *
FROM yourTable t1
INNER JOIN
(
    SELECT zip
    FROM yourTable
    GROUP BY zip
    HAVING COUNT(*) = 1
) t2
    ON t1.zip = t2.zip

or this:

SELECT *
FROM yourTable
WHERE zip IN
(
    SELECT zip
    FROM yourTable
    GROUP BY zip
    HAVING COUNT(*) = 1
)