I have a booking list where two same cars can be listed but I only want to show one and not the latest I want the earlist.
How I do it ?
SELECT
booking.id,
cars.name,
cars.cover as image,
car_details.mark,
client.surname,
client.lastname,
booking_car_status.booking_id as booking_car_status_id
FROM booking
INNER JOIN cars
ON booking.car_id = cars.id
INNER JOIN car_details
ON car_details.car_id = cars.id
INNER JOIN client
ON client.id = booking.client_id
LEFT JOIN booking_car_status
ON booking_car_status.booking_id = booking.id
LEFT JOIN booking_signature
ON booking_signature.booking_id = booking.id
WHERE (booking_car_status.booking_id IS NULL) AND (booking.from_date::date <= NOW()) ORDER BY booking.id ASC
>Solution :
I assumed that cars.id is indicating the uniqueness of cars and you want to have the earliest the booking.id for each car
WITH CTE AS
(
SELECT
booking.id,
cars.name,
cars.cover as image,
car_details.mark,
client.surname,
client.lastname,
booking_car_status.booking_id as booking_car_status_id ,
ROW_NUMBER()OVER(PARTITION BY cars.id ORDER BY booking.id)RN
FROM booking
INNER JOIN cars
ON booking.car_id = cars.id
INNER JOIN car_details
ON car_details.car_id = cars.id
INNER JOIN client
ON client.id = booking.client_id
LEFT JOIN booking_car_status
ON booking_car_status.booking_id = booking.id
LEFT JOIN booking_signature
ON booking_signature.booking_id = booking.id
WHERE (booking_car_status.booking_id IS NULL) AND (booking.from_date::date <= NOW())
)
SELECT id,
name,
image,
mark,
surname,
lastname,
booking_car_status_id
FROM CTE WHERE RN=1
ORDER BY id ASC