Get Earliest or Latest Date of Row Detail in MySQL

Question

I have a table like so. The way it works is that the billing occurs daily to make sure that accounts are current.

+------+------------+-------------+
| ID   | AcctType   | BillingDate |
+------+------------+-------------+
| 100  | Individual | 2020-01-01  |
| 100  | Individual | 2020-01-02  |
| 100  | Individual | 2020-01-03  |
| 101  | Group      | 2020-01-01  |
| 101  | Group      | 2020-01-02  |
| 101  | Individual | 2020-01-01  |
+------+------------+-------------+

What I need to find is the first and last AcctType of each plan by ID since the AcctType can change. I am using MySQL and the aggregation of select ID, AcctType, min(BillingDate) from table group by ID won't work because AcctType will return a random value associated with the ID. How do I reliably get the latest and earliest AcctType by ID? Using version 5.6.

Answer 1

If you are running MySQL 8.0, you can use window functions for this:

select distinct
    id,
    first_value(acctType) over(
        partition by id 
        order by billingDate 
        rows between unbounded preceding and unbounded following
    ) firstAccType,
    last_value(acctType) over(
        partition by id 
        order by billingDate 
        rows between unbounded preceding and unbounded following
    ) lastAccType
from mytable

This generates a single record for each id, with the first and last value of accType in columns.

In earlier versions, using correlated subquery is probably the simplest solution to achieve the same result:

select distinct 
    id,
    (
        select t1.accType 
        from mytable t1 
        where t1.id = t.id 
        order by billingDate asc
        limit 1
    ) firstAccType,
    (
        select t1.accType 
        from mytable t1 
        where t1.id = t.id 
        order by billingDate desc
        limit 1
    ) lastAccType
from mytable