sqlselectgreatest-n-per-groupfirebird

Get two or more rows from Firebird DB using max


I'm trying to get at least two rows from query using max. I have a query for getting all customers cars and need to combine this with last date of service and order number.

select
b.year,
b.class,
b.vin,
b.plates,
a.name,
a.street,
...
from cars b
left join customers a on a.id=b.customer

Now I need to get last order number and date of this order and found way to get last date using

(select max(x.order_date) from orders x where x.car=b.id)

How to get on the same time x.order_number from the same table?


Solution

  • If I understand correctly you could do something like this:

    select g.year,
           g.class,
           g.vin,
           g.plates,
           g.name,
           g.street,
           g.maxID,
           (select x.order_date from orders x where x.order_number = g.maxID) maxDate
      from ( 
             select b.year,
                    b.class,
                    b.vin,
                    b.plates,
                    a.name,
                    a.street,
                    (select max(x.order_number) from orders x where x.car=b.id) maxID
               from cars b
               left join customers a on a.id=b.customer
            ) g
    

    I can't really tell if that's the more efficient way, but will do the job.