If I would like to subtract the fields from each other,
i.e. in A there are 11 fields described as ‘Faktura zakupu’ and in B there are 5 fields described as ‘Faktura zakupu’. I would like to get a return of records in the form of 6 items ‘Faktura zakupu’ (11-5 = 6)
I tried the EXCEPT operation, but it does not return the desired results
what operation do i need to perform?
Advertisement
Answer
You can add row number to each row in both tables. Then SQL Server can determine that the first (Faktura zakupu, Original)
in table A is a duplicate of the first (Faktura zakupu, Original)
in table B and remove it during EXCEPT
operation:
SELECT Name, StatusReq, ROW_NUMBER() OVER (PARTITION BY Name, StatusReq ORDER BY (SELECT NULL)) FROM a EXCEPT SELECT Name, StatusReq, ROW_NUMBER() OVER (PARTITION BY Name, StatusReq ORDER BY (SELECT NULL)) FROM b
It’ll return 6 rows from table A… numbered 6 through 11.