CODEWITHSUNDEEP
ms-access
John Smith
John Smith

Reputation: 12266

How to only return one row per met criteria in Access SQL?

I have data that looks like this

CUSTID     POLICY     TYPE   RANDOMTHING   ETC

And sometimes there will be multiply rows per CUSTID, with different values for the rest of the fields otherwise. I only want to return one row per CUSTID, taking only the first row's values (ignoring the rest).

I've tried applying First to CUSTID but it doesn't seem to work.

Upvotes: 1

Views: 1719

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123849

I believe you want something like

SELECT t1.* 
FROM yourTable t1 INNER JOIN 
    (
        SELECT CUSTID, MIN(POLICY) AS MinOfPOLICY 
        FROM yourTable
        GROUP BY CUSTID
    ) t2 ON t1.CUSTID = t2.CUSTID AND t1.POLICY = t2.MinOfPOLICY
ORDER BY 1

In this example, t1 and t2 are table aliases. In most cases when constructing a query that references the same table more than once, table aliases are required to disambiguate column references. For example, you can't just say CUSTID because it's unclear as to whether you mean CUSTID from the first instance of yourTable, or from the second instance.

The SQL language allows tables to be given an alias by placing the alias immediately after the table name, so yourTable t1 means "yourTable, using the alias t1". The other alias, t2, is the alias for the GROUP BY subquery (the one inside the brackets).

Upvotes: 1

Related Questions