how to optimize multiple unions for the same table with different condition into a single query



  • Environment : PostgreSQL v10.6 (AWS RDS instance) For the given table:

    CREATE temp TABLE lve
        ("fin" timestamptz, "vehiculo" int, "equipo" int)
    ;
    

    INSERT INTO lve
    ("fin", "vehiculo", "equipo")
    VALUES
    ('2021-12-01 00:00:00', 1, 888),
    (NULL, 3, 888),
    ('2021-05-01 00:00:00', 2, 888),
    ('2021-11-05 00:00:00', 10, 333),
    (NULL, 9, 333),
    ('2021-09-05 00:00:00', 5, 333)
    ;

    I need the last record (by 'fin' column) where 'fin' is not null, for every value in a given list of 'equipo'.

    For a single, given 'equipo', this query works:

    select * from lve
    where equipo = 333 and fin is not null
    order by fin desc
    limit 1
    

    for two 'equipo' , with a UNION I can get my desired result:

    (select * from lve
    where equipo = 333 and fin is not null
    order by fin desc
    limit 1)
    union 
    (select * from lve
    where equipo = 888 and fin is not null
    order by fin desc
    limit 1);
    

    result:

    fin vehiculo equipo
    2021-11-05 00:00:00.000 -0300 10 333
    2021-12-01 00:00:00.000 -0300 1 888

    But since in reality the table contains much more data for many others vehiculo/equipo and I can have a list with N 'equipo' to query for, it's not optimal to continue adding multiple UNIONs manually.

    Is there a way that I can rewrite this into a single query where i can just pass the list/array of given 'equipo' (888, 333, nnn) ?

    PD: My plan B is to create a function that returns my desired record for a single 'equipo' and then use that with an array, but I really want to know if this can be done in the way I'm asking.

    Thanks.



  • You can use row_number() to order by fin for each equipo and then choose the first/last from there:

    select fin, vehiculo, equipo
    from (
      select fin, vehiculo, equipo
         , row_number() over (partition by equipo order by fin desc) as rn
      from  lve
      where fin is not null
    ) as t
    where rn = 1;
    

    fin vehiculo equipo
    2021-11-05 00:00:00+00 10 333
    2021-12-01 00:00:00+00 1 888

    See https://dbfiddle.uk/?rdbms=postgres_10&fiddle=a4f235fb262858ede4cc6b5fa7fef28a




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2