SQL query which filters rows based on a duplicate value

Question

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.

Gordon Linoff · Accepted Answer · 2014-10-06 15:06:42Z

2

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.

edited Oct 6, 2014 at 15:06

answered Oct 2, 2014 at 22:41

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eddie Over a year ago

Thanks for the help. I used your SQL subquery in my database, adapted it a little and it worked a treat.

Collectives™ on Stack Overflow

SQL query which filters rows based on a duplicate value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related