Batuhan Ozdal Batuhan Ozdal - 7 months ago 25
SQL Question

Order by with distinct

Firstly my sql:

SELECT DISTINCT(CarTable.CarPlate), CarTable.CarImage, EventTable.FirstDate
FROM CarTable JOIN EventTable ON CarTable.CarId = EventTable.CarId
ORDER BY 3 DESC


I am trying order rows by date and that works fine but I want all plates to be written only one time. I mean I need to see all car's last event but it seems I am using distinct wrong. What is the best way of doing this ?

Answer

Use MAX and GROUP BY:

SELECT 
    c.CarPlate, 
    c.CarImage, 
    FirstDate = MAX(e.FirstDate)
FROM CarTable c
INNER JOIN EventTable e
    ON c.CarId = e.CarId
GROUP BY c.CarPlate, c.CarImage
ORDER BY MAX(e.FirstDate) DESC

Note:

  • Use meaningful alias to improve readability.
Comments