How to select one email per record

Question

Can anyone help me how to select one record per email?

I have the query below:

SELECT a.ID, a.NAME, a.LASTMODIFIED, b.EMAIL
FROM TABLE_A a
JOIN TABLE_B b
ON a.IDA = b.IDB
WHERE a.LASTMODIFIED <= today
ORDER BY b.LASTMODIFIED

it will result :

+------+-------+--------------------------------------+
| id   | name   | lastmodified      | email           |
+------+-------+--------------------------------------+
| 1    | aa     | 01-JAN-2016       | [email protected] |
| 2    | bb     | 02-JAN-2016       | [email protected] |
| 3    | cc     | 03-JAN-2016       | [email protected] |
| 4    | dd     | 02-JAn-2016       | [email protected] |
+------+-------+--------------------------------------+

expected result is :

+------+-------+--------------------------------------+
| id   | name  | lastmodified       | email           |
+------+-------+--------------------------------------+
| 2    | bb    | 02-JAN-2016        | [email protected] |
| 3    | cc    | 03-JAN-2016        | [email protected] |
| 4    | dd    | 02-JAN-2016        | [email protected] |
+------+-------+--------------------------------------+

It should return only one email per row, order by lastmodified date.

Answer 1

If you want the last email you can use

SELECT a.ID, a.NAME, a.LASTMODIFIED, b.EMAIL
FROM TABLE_A a
JOIN TABLE_B b ON a.IDA = b.IDB
WHERE a.LASTMODIFIED <= today
AND   (a.LASTMODIFIED,  b.EMAIL) in  (
    SELECT max(c.LASTMODIFIED), d.EMAIL
            FROM TABLE_A c
            JOIN TABLE_B d ON c.IDA = d.IDB
            WHERE a.LASTMODIFIED <= today
            GROUP BY d.EMAIL
    )
ORDER BY b.LASTMODIFIED