MSQuery how to inner join 2 tables with allias (excel odbc)

Question

I've been trying to get the last row of each ean (id) because it has several TimeStamps (tmh), but I can't get it on MSQuery with ODBC conexions...Some suggests?

SELECT TMH, PESO, ALTO, ANCHO, LARGO, EAN FROM 

(SELECT TMH219 as TMH, PES219 AS PESO, ALT219 AS ALTO, ANC219 AS ANCHO, LAR219 AS LARGO, EAN219 AS EAN FROM SGAVDL.SGA21900) T1

INNER JOIN (SELECT EAN219, MAX(TMH219) FROM SGAVDL.SGA21900) T2

ON T1.EAN219 = T2.EAN219

GROUP BY  TMH, PESO, ALTO, ANCHO, LARGO, EAN

Thank you in advance!

`select max(timestamp)`? – WhatsThePoint Sep 28 '17 at 10:31 — WhatsThePoint, Sep 28 '17 at 10:31

Rams · Accepted Answer · 2017-09-28T11:12:09.087

1

With below query you should get

SELECT T1.TMH219 as TMH, PES219 AS PESO, ALT219 AS ALTO, ANC219 AS ANCHO, LAR219 AS LARGO, T1.EAN219 AS EAN FROM 
SGAVDL.SGA21900 T1 INNER JOIN (SELECT MAX(TMH219) TMH219 FROM SGAVDL.SGA21900) T2
ON T1.TMH219=T2.TMH219

edited Sep 28 '17 at 11:12

answered Sep 28 '17 at 10:33

Rams

2,129
1
12
19

score 1 · Answer 2 · edited Sep 28 '17 at 13:30

I could solve it in a easy way, i wasnt grouped by the second table t2, and i wasnt put the equality of TMH in ON parameter:

SELECT T2.MTMH, T1.PESO, T1.ALTO, T1.ANCHO, T1.LARGO, T1.EAN FROM 
(SELECT TMH219 as TMH, PES219 AS PESO, ALT219 AS ALTO, ANC219 AS ANCHO, LAR219 AS LARGO, EAN219 AS EAN FROM SGAVDL.SGA21900) T1
RIGHT JOIN 
     (SELECT EAN219, MAX(TMH219) AS MTMH FROM SGAVDL.SGA21900 
       WHERE DATE(TMH219) = DATE(CURRENT_DATE) GROUP BY EAN219) T2
ON T1.EAN = T2.EAN219 and t1.TMH = T2.MTMH
GROUP BY EAN, MTMH, PESO, ALTO, ANCHO, LARGO

I think it is more or less what @Stanislovas means, but he simplified very well

Thnks!

MSQuery how to inner join 2 tables with allias (excel odbc)

2 Answers2