Turning Regular Select Statements Into Correlated Subquery?

Question

I have my following SQL query:

 SELECT name, transaction_id, MIN(transaction_date)
 FROM clients
 JOIN transactions ON clients.client_id = transactions.client_id
 GROUP BY name, transaction_id;

That I'd like to turn into a correlated subquery with the following structure:

 SELECT a, b, MIN(c)
 FROM t1
 JOIN t2 ON t1.d = t2.d
 WHERE c IN
      (SELECT * 
      FROM t2
      HAVING....)

Where a, b, c are column-names and t1, t2 are tables.

But I'm having difficulty in the process.

For reference, the original problem is asking to return the earliest transaction_date of each customer along with its corresponding transaction_id.

So, if the transactions table had the following:

   transaction_id    client_id      transaction_date
         1               1              02-02-17
         2               1              02-01-17
         3               2              02-03-17
         4               2              02-04-17

The correlated subquery would return:

    name       transaction_id       transaction_date
    John            2                   02-01-17
    Mary            3                   02-03-17

Answer 1

Your query does not do what you think it does. A proper query would be:

SELECT c.name, t.transaction_id, t.transaction_date
FROM clients c JOIN
     transactions t
     ON c.client_id = t.client_id
WHERE t.transaction_date = (SELECT MIN(t2.transaction_date)
                            FROM transactions t2
                            WHERE t2.client_id = t.client_id
                           );

A more typical query would be:

SELECT name, transaction_id, transaction_date
FROM (SELECT c.name, t.transaction_id, t.transaction_date,
             ROW_NUMBER() OVER (PARTITION BY c.client_id ORDER BY t.transaction_date) as seqnum
      FROM clients c JOIN
           transactions t
           ON c.client_id = t.client_id 
     ) ct
WHERE seqnum = 1;