SQL Exclude rows previously selected in another category

Question

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?

Answer 1

This would give your desired results

SELECT MIN(Site) AS Site,
       Data
FROM   dbo.Sample
GROUP  BY Data
ORDER  BY Site