Reputation: 12266
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
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