Q:

MySQL best way to select first, second and last value in each group

I'm trying to use MySQL to get first, second and last values in each group in a table. My data rows are like this:
userID    purchaseTime
----------------------
1         2018-01-01
1         2018-01-02
1         2018-01-03
1         2018-01-04
2         2018-02-01
2         2018-02-02
3         2018-03-01

The expected result would be: 
userID    first         second        last
------------------------------------------------
1         2018-01-01    2018-01-02    2018-01-04
2         2018-02-01    2018-02-02    2018-02-02
3         2018-03-01    null          2018-03-01

After googling for half day, I can only figure out a stupid way that executes the following two queries RESPECTIVELY then merge the results by my server-side code:
//get 1st, 2nd values 
SELECT userID, purchaseTime
FROM   purchaseLog t1
WHERE 
    (
        SELECT  COUNT(*) 
        FROM    purchaseLog t2
        WHERE t2.userID = t1.userID AND 
              t2.purchaseTime<= t1.purchaseTime
    ) <= 2 order by t1.userID , t1.purchaseTime;

//get last value
SELECT max(purchaseTime) FROM purchaseTime GROUP BY userID 

I'm pretty sure that there must be a more elegant way to get the results in one shot. Can anyone help me to reach my requirement? thank you all!

A:

The following code is untested but should give you a good idea:
SELECT
    t1.userID,
    t1.purchaseTime AS first,
    t2.purchaseTime AS `second`,
    t4.purchaseTime AS last
FROM purchaseLog t1
LEFT JOIN purchaseLog t0 ON t1.userID = t0.userID AND t0.purchaseTime < t1.purchaseTime
LEFT JOIN purchaseLog t2 ON t1.userID = t2.userID AND t1.purchaseTime < t2.purchaseTime
LEFT JOIN purchaseLog t3 ON t1.userID = t3.userID AND t1.purchaseTime < t3.purchaseTime
                        AND t3.purchaseTime < t2.purchaseTime
JOIN purchaseLog t4 ON t1.userID = t4.userID AND t1.purchaseTime <= t4.purchaseTime
LEFT JOIN purchaseLog t5 ON t1.userID = t5.userID AND t4.purchaseTime < t5.purchaseTime
WHERE t0.purchaseTime IS NULL AND t3.purchaseTime IS NULL AND t5.purchaseTime IS NULL

Let me break that down step-by-step:
First, I get all the rows for which no earlier row for the same userID exists:
SELECT
    t1.userID,
    t1.purchaseTime AS first
FROM purchaseLog t1
LEFT JOIN purchaseLog t0 ON t1.userID = t0.userID AND t0.purchaseTime < t1.purchaseTime
WHERE t0.purchaseTime IS NULL

Next, I get all the rows with a purchaseTime greater than the first purchaseTime for which there's no rows with a purchaseTime in-between the two:
SELECT
    t1.userID,
    t2.purchaseTime AS `second`
FROM purchaseLog t1
LEFT JOIN purchaseLog t2 ON t1.userID = t2.userID AND t1.purchaseTime < t2.purchaseTime
LEFT JOIN purchaseLog t3 ON t1.userID = t3.userID AND t1.purchaseTime < t3.purchaseTime
                        AND t3.purchaseTime < t2.purchaseTime
WHERE t3.purchaseTime IS NULL

Finally, I get the rows with a purchaseTime greater than or equal to the first for which no greater purchaseTime exists:
SELECT
    t1.userID,
    t4.purchaseTime AS last
FROM purchaseLog t1
JOIN purchaseLog t4 ON t1.userID = t4.userID AND t1.purchaseTime <= t4.purchaseTime
LEFT JOIN purchaseLog t5 ON t1.userID = t5.userID AND t4.purchaseTime < t5.purchaseTime
WHERE t5.purchaseTime IS NULL

Combine them all into one query to get the answer above.

