CODEWITHSUNDEEP
sqlpostgresql
JorgeeFG
JorgeeFG

Reputation: 5961

How to do join only with distinct items with latest date

 SELECT t.aerop_salida,
    t.aerop_llegada,
    t.duracion,
    p.precio,
        p.fecha_hora_actualizacion
   FROM tramo t,
    vuelo v,
    tramo_precio p
  WHERE ((t.aerop_salida = v.aerop_salida) AND (t.aerop_llegada = v.aerop_llegada) AND ((p.tramo_aerolinea)::text = (t.aerolinea)::text) AND (p.tramo_aerop_salida = t.aerop_salida) AND (p.tramo_aerop_llegada = t.aerop_llegada));

Would output

EZE LAX 15:25:00    12399   2017-10-10 19:50:58
EZE LAX 15:25:00    11900   2017-10-17 19:51:29
EZE LAX 16:20:00    12390   2017-10-15 23:55:03
AEP IGR 02:45:00    3200    2017-10-16 10:55:27
IGR LIM 03:15:00    5600    2017-10-11 20:00:41

But should only output EZE - LAX trip with price 11900 as this is the current one (was updated on 2017 10 17)

EDIT:

I'm trying something like this but giving me syntax error:

 SELECT t.aerop_salida,
    t.aerop_llegada,
    t.duracion,
    p.precio,
        p.fecha_hora_actualizacion
   FROM tramo t,
    vuelo v,
    (select DISTINCT tp.tramo_aerolinea, tp.tramo_aerop_llegada, tp.tramo_aerop_salida FROM tramo_precio tp order by tp.fecha_hora_actualizacion desc) p
  WHERE ((t.aerop_salida = v.aerop_salida) AND (t.aerop_llegada = v.aerop_llegada) AND ((p.tramo_aerolinea)::text = (t.aerolinea)::text) AND (p.tramo_aerop_salida = t.aerop_salida) AND (p.tramo_aerop_llegada = t.aerop_llegada));

Upvotes: 0

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

First, never use commas in the FROM clause. Always use proper, explicit JOIN syntax. This is the Twenty-First Century, after all.

You can do what you want using DISTINCT ON:

SELECT DISTINCT ON (t.aerop_salida, t.aerop_llegada) t.aerop_salida, t.aerop_llegada, t.duracion,
       p.precio, p.fecha_hora_actualizacion
FROM tramo t JOIN
     vuelo v
     ON t.aerop_salida = v.aerop_salida AND
        t.aerop_llegada = v.aerop_llegada JOIN
        tramo_precio p
        ON (p.tramo_aerolinea)::text = (t.aerolinea)::text) AND 
           (p.tramo_aerop_salida = t.aerop_salida) AND 
           (p.tramo_aerop_llegada = t.aerop_llegada)
ORDER BY t.aerop_salida, t.aerop_llegada, p.fecha_hora_actualizacion DESC;

Upvotes: 1

sotondolphin
sotondolphin

Reputation: 223

if col3 contains the date column

select * from A 
inner join 
(select distinct t.col1, t. col2 
 from
    (select col1, col2, col3
     from table
     grouping by col1, col2
     having max(col3)
     ) t
)outer
on a.col1 = outer.col1

Upvotes: 0

Related Questions