SQL Exclude rows previously selected in another category
sql-server, sql-server-2008
Solution
This would give your desired results
SELECT MIN(Site) AS Site,
Data
FROM dbo.Sample
GROUP BY Data
ORDER BY Site
Problem
``` SELECT * FROM dbo.Sample ORDER BY Site ``` Output: ``` Site Data 1 Cat 1 Mule 1 Pig 2 Giraffe 2 Dog 2 Horse 2 Mule 4 Cat 8 Dog 8 Pig 8 Mule 8 Bull 9 Bull 9 Giraffe 9 Moose ``` I want to display the Data sorted by Site, but exclude rows that were in a previous Site. Show all items in Site 1 Any items in Site 2 that were not already shown in Site 1, etc. So the result show be: ``` Site Data ---- ---- 1 Cat 1 Mule 1 Pig 2 Dog 2 Giraffe 2 Horse 9 Moose ``` The initial list is built from a query with several joins. A working example would be: ``` SELECT * FROM dbo.Sample WHERE Site = 1 UNION SELECT * FROM dbo.Sample WHERE Site = 2 AND Data NOT IN (SELECT Data FROM dbo.Sample WHERE Site < 2) UNION SELECT * FROM dbo.Sample WHERE Site = 8 AND Data NOT IN (SELECT Data FROM dbo.Sample WHERE Site < 8) UNION SELECT * FROM dbo.Sample WHERE Site = 9 AND Data NOT IN (SELECT Data FROM dbo.Sample WHERE Site < 9) ORDER BY Site ``` My list of Sites comes from a query as well. SELECT Distinct Site from SiteList ``` 1 2 4 8 9 ``` Surely there should be an easy and efficient way to do this?