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