CODEWITHSUNDEEP
mysqlsql
Martijn19
Martijn19

Reputation: 189

Mysql select unique value + first occurrence + last occurence

I'm having trouble creating a query that returns the following from a table containing transactions. The table looks something like this:

id address_from address_to timestamp

So I want to create a list that contains:

All unique addresses and their first transaction and their last.

I have no problem selecting all unique addresses like this:

SELECT address_from as address 
  FROM transactions 
UNION 
SELECT address_to as address 
  FROM transactions

But how can I add the first timestamp that address appeared in either address_from or address_to and the last? Some hints in the right direction would be highly appreciated.

Upvotes: 0

Views: 793

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Is this what you want?

SELECT address, min(timestamp), max(timestamp)
FROM ((SELECT address_from as address, timestamp 
       FROM transactions 
      )
      UNION ALL
      (SELECT address_to as address, timestamp
       FROM transactions
      )
     ) a
GROUP BY address;

This has the timestamps for the addresses, but not the ids. It is unclear which you are looking for.

Upvotes: 0

Related Questions