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.
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
)
This will also work
select
id, name, zip
from
(select
id, name, zip, count(zip) as countZip
from
student
group by zip) as subq
where
countZip = '1'
There is a DISTINCT modifier that forces a select to show only one value for a column that may appear several times. In your case it would be:
SELECT DISTINCT ZIP
FROM ...
