screw4445 screw4445 - 1 year ago 52
SQL Question

PostgreSQL show latest date and one value of a column from many

I have a problem with a query, that i can't figure out. Have tried for some time, but I just can't figure it out. Would be a great deal of help if you could help me. So... I have 4 tables:

cars - ID, make, model, plate_number, price, type, year, owner_ID
persons - ID, name, surname, pers_code
insurance_data - company_ID, car_ID, first_date, last_date
companies - ID, title

My query so far is..

SELECT cars.plate_number,, persons.surname, insurance_data.last_date
FROM cars,persons,insurance_data
WHERE cars.owner_ID = persons.ID AND cars.ID = insurance_data.car_ID

This outputs cars plate number, owner of the car, and the last date of the car's insurance. But the problem is that there's two cars that have two end dates of insurance, so in the output there's two entries for same car and with both insurance end dates. What i need is that there would be only one entry for each car and corresponding insurance end date should be the latest.

I know this is pretty basic, but i'm a first year student of databases, and this is one of my first assignments. Thanks in advance

Answer Source

(1) Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.

(2) Use table aliases!

The answer to your question is DISTINCT ON:

SELECT DISTINCT ON (c.plate_number) c.plate_number,, p.surname, id.last_date
FROM cars c JOIN
     persons p
     ON c.owner_ID = p.ID JOIN
     insurance_data id
     ON c.ID = id.car_ID
ORDER BY c.plate_number, id.last_date DESC;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download