Using SQL to select unique records

[PaulBarter]

I need to create a SQL statement (actually for a control in VB6 but also applies in access) to select only unique values of 2 fields:

eg

SELECT Field1, Field2, Field3, Field4 FROM MyTable ORDER BY Field2, Field3;

but I only want 1 record for any unique Field2 & Field3 combination.

If I use DISTINCT then it automatically include Field1 and Field4 in it's uniqueness test.

I am using Access 2000

I have put a post in VB6 forum but it may be more relvant here!

 

[Golom]

You could use

SELECT MAX(Field1) As MaxF1, Field2, Field3, MAX(Field4) As MaxF4

FROM MyTable 

GROUP BY Field2, Field3

ORDER BY Field2, Field3;

That will give you unique records for Field2 & Field3 but the values returned for Fields 1 and 4 will not necessarily be from the same record in your base table. You can also use MIN, FIRST, LAST, AVERAGE, SUM, etc. as aggregate functions if those work better for you.