CODEWITHSUNDEEP
sqlms-accessvbams-access-2007
Eddie
Eddie

Reputation: 621

SQL query which filters rows based on a duplicate value

I have an MS Access query which displays the below data.

enter image description here

I want to write some SQL which, when executed, displays the below result.

enter image description here

The rule I have is as follows, and is based on Field5.

If the value in Field5 <> 'C' then only display 1 row for any other value.

I started playing around with subqueries and using the First operator but because all the other values in my table are unique, all the rows still get displayed.

This is my poor attempt:

SELECT T.Field1, T.Field2, T.Field3, T.Field4, First(T.Field5) AS FirstOfField5
FROM Table1 as T
GROUP BY T.Field1, T.Field2, T.Field3, T.Field4
ORDER BY First(T.Field5);

Any help would be great.

Upvotes: 1

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

If we assume that field1 is unique on each row, you can do:

SELECT T.Field1, T.Field2, T.Field3, T.Field4, T.Field5
FROM Table1 as T JOIN
     (SELECT t.Field5, MIN(t.Field1) as Field1
      FROM table1
      GROUP BY t.Field5
     ) as tt
     ON tt.Field1 = t.Field1 or t.Field5 = 'C';

This would be much harder if you do not have a unique identifier for a row.

Upvotes: 2

Related Questions