SQL Server Performance Forum – Threads Archive

Count Based Select

Is it possible to construct a query in Transact SQL that only returns rows where the values of each of three fields in the table are not present in other rows in that table? In invalid "pseudo-sql" this might look like: SELECT id, @length = length , @width = width, @height = height FROM widgets WHERE
(SELECT count FROM widgets WHERE @length = length) = 1 AND
(SELECT count FROM widgets WHERE @width = width) = 1 AND
(SELECT count FROM widgets WHERE @height = height) = 1 Thanks in advance! UTH

---

Use the NOT EXISTS clause, like this: SELECT id, length , width, height
FROM widgets
WHERE NOT EXISTS (SELECT T.id FROM widgets T WHERE T.length = widgets.length AND T.id <> widgets.id)
AND NOT EXISTS (SELECT T.id FROM widgets T WHERE T.width = widgets.width AND T.id <> widgets.id)
AND NOT EXISTS (SELECT T.id FROM widgets T WHERE T.height = widgets.height AND T.id <> widgets.id) If it’s the combination of L & W & H that you want to be unique, a single NOT EXISTS with the combined criteria will suffice. [EDIT: Forgot to put in the T alias next to the table name in the subqueries …]

---

Adriaan, Thank you so much for the suggestion, it works perfectly. I would definitely not have thought of that! UTH

---

]]>