Get two or more rows from Firebird DB using max

Question

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?

Answer 1

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.