Mario Cordeiro Mario Cordeiro - 6 months ago 24
SQL Question

MySQL SELECT two tables

I have two tables:

Table_1:

| ID | numero | apagado
| 1 | 23 | 0
| 2 | 56 | 0
| 3 | 156 | 0
| 4 | 48 | 0


Table_2:

| ID_Table_1 | data_inspecao |
| 1 | 2014-01-03 |
| 2 | 2014-01-08 |
| 1 | 2014-02-20 |
| 4 | 2014-01-06 |
| 2 | 2014-01-23 |


I want to get the most recent 'data_inspecao' for all the 'numero'.

In my example I want the following result:

| ID_Table_1 | data_inspecao |
| 1 | 2014-02-20 |
| 2 | 2014-01-23 |
| 4 | 2014-01-06 |


I have the following SELECT statement, but if I change 'DESC' for 'ASC' the result is the same, I did not get the most recent 'data_inspecao':

SELECT
e.numero, i.data_inspecao
FROM
table_1 e
INNER JOIN
table_2 i ON i.ID_Table_1 = e.numero
WHERE
e.numero = 6 AND e.apagado = 0
GROUP BY
e.numero
ORDER BY
i.data_inspecao DESC


Any help is appreciated

Answer

using your example with added MAX aggregation

SELECT 
    e.numero, Max(i.data_inspecao) as MostRecentDate 
FROM 
    table_1 e 
INNER JOIN 
    table_2 i ON i.ID_Table_1 = e.numero 
GROUP BY 
    e.numero 
ORDER BY 
   e.numero