Select UNIQUE, NOT DISTINCT values-CodePudding

I am trying to select values from a table that are not duplicates - for example, with the following input set, I would like to select only the values in Column 1 that don't have a duplicated value in Column 2

Column 1    Column 2
A           X
B           X
C           Y
D           Y
E           Z

Resulting in

Column 1    Column 2
E           Z

This is made harder by my having a character limit for my SQL statement, and my having to join a couple of tables in the same query.

My existing statement is here, and this is where I am stuck.

SELECT d.o_docguid, d.o_itemdesc
FROM dms_doc d
INNER JOIN
(SELECT s.o_itemno as si, s.o_projectno as sp, t.o_itemno as ti, t.o_projectno as tp
FROM env_bs1192_1 s, env_bs1192_2 t
WHERE s.TB_FILE_ID = t.TB_FILE_ID) as r
ON (si = d.o_itemno AND sp = d.o_projectno)
OR (ti = d.o_itemno AND tp = d.o_projectno)

Results look like

o_docguid    o_itemdesc
aguid        adescription
bguid        adescription
cguid        bdescription

I want to filter this list out such that all that remains are the unique descriptions and their associated guid (i.e. only the rows that have specifically a single unique entry in the description, or put another way, if there is a duplicate, throw both away - in this instance, cguid and bdescription should be the only results).

The last challenge, which I still haven't solved, is that this SQL statement needs to fit into a character limit of 242 characters.

CodePudding user response：

Taking the first part as a question, the answer might be:

declare @Table table (Column1 char(1), Column2 char(1));

insert into @Table values 
    ('A', 'X'),
    ('B', 'X'),
    ('C', 'Y'),
    ('D', 'Y'),
    ('E', 'Z');

select
    Column1 = max(Column1),
    Column2
from
    @Table
group by
    Column2
having 
    count(*) = 1;

CodePudding user response：

How to do it with generic data.

DROP TABLE IF EXISTS #MyTable

CREATE TABLE #MyTable(Column1 VARCHAR(50),Column2 VARCHAR(50))
INSERT INTO #MyTable(Column1,Column2)
VALUES
('A','X'),
('B','X'),
('C','Y'),
('D','Y'),
('E','Z')

;WITH UniqueCol2 AS
(
SELECT Column2
FROM #MyTable
GROUP BY Column2
HAVING COUNT(*) = 1
)
SELECT
mt.*
FROM UniqueCol2
JOIN #MyTable mt ON mt.Column2 = UniqueCol2.Column2