Exclude the specific kind of record

Question

I am using SQL Server 2008 R2. I do have records as below in a table :

Id  Sys   Dia   Type        UniqueId
1   156   20    first       12345
2   157   20    first       12345
3   150   15    last        12345
4   160   17    Average     12345
5   150   15    additional  12345
6   157   35    last        891011
7   156   25    Average     891011
8   163   35    last        789521
9   145   25    Average     789521
10  156   20    first       963215
11  150   15    last        963215
12  160   17    Average     963215
13  156   20    first       456878
14  157   20    first       456878
15  150   15    last        456878
16  160   17    Average     456878
17  150   15    last        246977
18  160   17    Average     246977
19  150   15    additional  246977

Regarding this data, these records are kind of groups that have common UniqueId. The records can be of type "first, last, average and additional". Now, from these records I want to select "average" type of records only if they have "first" or "additional" kind of reading in group. Else I want to exclude them from selection..

The expected result is :

Id  Sys   Dia   Type        UniqueId
1   156   20    first       12345
2   157   20    first       12345
3   150   15    last        12345
4   160   17    Average     12345
5   150   15    additional  12345
6   157   35    last        891011
7   163   35    last        789521
8   156   20    first       963215
9   150   15    last        963215
10  160   17    Average     963215
11  156   20    first       456878
12  157   20    first       456878
13  150   15    last        456878
14  160   17    Average     456878
15  150   15    last        246977
16  160   17    Average     246977
17  150   15    additional  246977

In short, I don't want to select the record that have type="Average" and have only "last" type of record with same UniqueId. Any solution?

Answer 1

Try something like this:

SELECT * FROM MyTable WHERE [Type] <> 'Average'
UNION ALL
SELECT * FROM MyTable T WHERE [Type] = 'Average'
    AND EXISTS (SELECT * FROM MyTable 
                WHERE [Type] IN ('first', 'additional') 
                    AND UniqueId = T.UniqueId)

The first SELECT statement gets all records except the ones with Type = 'Average'. The second SELECT statement gets only the Type = 'Average' records that have at least one record with the same UniqueId, that is of type 'first' or 'additional'.

Answer 2

Using EXISTS operator along correlated sub-query:

SELECT * FROM dbo.Table1 t1
WHERE [Type] != 'Average' 
OR EXISTS (SELECT * FROM Table1 t2 
           WHERE t1.UniqueId = t2.UniqueId 
             AND t1.[Type] = 'Average' 
             AND t2.[Type] IN ('first','additional'))

SQLFiddle DEMO