0

New here and New to coding. I am tring to create a query statement for this to find out the model of the car that was the first car reserved (i.e., it has the earliest reservation date and time)?

Here is the model i am working with reservation,car and owner details

and there is my code. I am thinking i shouldnt order by reservation time. I need your thoughts.

SELECT carid, carmodel
FROM car 
JOIN reservation ON reservation.cari d =car.carid
WHERE resvbegdate= (
    SELECT carid, MIN(resvbegdate)
    FROM reservation
    GROUP BY carid 
    ORDER BY resvbegtime
);

Thank you!

GMB
  • 216,147
  • 25
  • 84
  • 135
Jay
  • 9
  • 1

1 Answers1

0

find out the model of the car that was the first car reserved (i.e., it has the earliest reservation date and time)

No need for aggregation for this: you can just join, sort the results by reservation date, and fetch the first record only.

select c.carid, c.carmodel 
from car c
join reservation r on r.carid = c.carid 
order by r.resvbegdate desc, r.resvbegtime desc
limit 1
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thank you so much. I haven't come across the limit function before now. Is it OK if i include the resvbegtime in the 'order by' as well?? Thanks again! – Jay Feb 29 '20 at 22:39
  • @Jay: sure. Side note: you would better have a single column to store the date *and* time of each reservation, in datatype `datetime`. – GMB Feb 29 '20 at 22:53
  • Very helpful! Thank you – Jay Feb 29 '20 at 22:56