Table1
NoteNr | TransactionID
----------------------
4711 | NULL
4711 | 123
4812 | NULL
4913 | 444
4610 | NULL
4610 | NULL
I want to have a query which produces the following result
4812 NULL
4610 NULL
So if any entry of the same NoteNr has a value in its TransactionID Column, the NoteNr should not be in the result of the query. The Resultset should only contain NoteNrs, if all entries of the NoteNr has no value in its TransactionID Column (NULL)
This should work:
SELECT DISTINCT *
FROM Table1 t
WHERE NOT EXISTS ( SELECT 1
FROM Table1
WHERE NoteNr = t.NoteNr
AND TransactionId IS NOT NULL );
This should do it
select NoteNr, NULL From (
select NoteNr, sum(TransactionID) as c From [table] group by NoteNr
) as t1 where t1.c IS NULL
USE CAN USE CTE to achieve this
;WITH CTE
AS
(
SELECT *,ROW_NUMBER()OVER(PARTITION BY NoteNr ORDER BY NoteNr)Rownum FROM #TEMP a WHERE TransactionID IS NULL AND NoteNr NOT IN (SELECT NoteNr FROM #TEMP WHERE TransactionID IS NOT NULL)
)
SELECT NoteNr,TransactionID FROM CTE WHERE Rownum=1
This will help.
SELECT NoteNr
,TransactionID
FROM (
SELECT DISTINCT NoteNr
,TransactionID
,ROW_NUMBER() OVER (
PARTITION BY NoteNr ORDER BY NoteNr
) AS rowno
) AS main
WHERE rowno = 1
This is the most efficient way to avoid the grouping needed to remove duplicates:
Select notenr, Null as TransactionId from @table1
except
select notenr, Null as TransactionId from @table1 where TransactionID is not null
MIN(CASE WHEN TransactionID IS NULL THEN 0 ELSE 1 END) = 0but since in NoteNr4711is one entry NULL, this is not working