CODEWITHSUNDEEP
sqlsql-server
niedarek
niedarek

Reputation: 3

MSSQL 2008 - select only rows where column has unique values never duplicated

I need select not duplicted values, which never has been duplicated, use distinct is not solution. I need show more fields from row than one, which is used to grouping.

My query which show only one field:

SELECT NIPL
FROM KONTRAHENT  GROUP BY NIPL HAVING (COUNT(NIPL)=1) 
ORDER BY NIPL DESC

Table:

id | NIPL
 1 | 230
 2 | 450   <- I want to select only this row
 3 | 230
 3 | 123
 4 | 123

Now my select return only NIPL. Simple adding id to statment is not woking. After long googling i'm give up. I need yours help :)

Upvotes: 0

Views: 1095

Answers (1)

Hogan
Hogan

Reputation: 70513

You just need to use a subquery.

Like this:

SELECT * 
FROM KONTRAHENT
WHERE NIPL IN 
(
   SELECT NIPL
   FROM KONTRAHENT
   GROUP BY NIPL
   HAVING COUNT(NIPL)=1 
)
ORDER BY NIPL DESC

Or this:

SELECT * 
FROM KONTRAHENT
JOIN 
(
   SELECT NIPL
   FROM KONTRAHENT
   GROUP BY NIPL
   HAVING COUNT(NIPL)=1 
) AS SUB ON KONTRAHENT.NIPL = SUB.NIPL
ORDER BY KONTRAHENT.NIPL DESC

Or (in systems that allow CTEs) this:

WITH SUB AS
(
   SELECT NIPL
   FROM KONTRAHENT
   GROUP BY NIPL
   HAVING COUNT(NIPL)=1 
) 
SELECT * 
FROM KONTRAHENT
JOIN SUB ON KONTRAHENT.NIPL = SUB.NIPL
ORDER BY KONTRAHENT.NIPL DESC

Upvotes: 2

Related Questions