Tuesday, May 22, 2012

PostgreSQL sorting NULLS

Here is a handy feature I didn't know existed until today.

You can sort null values to the top or bottom of a list by adding "NULLS FIRST" or "NULLS LAST" at the end of the ORDER BY clause.

For a real world example,
SELECT DISTINCT p.permit_id,city_permit_num,street_no,
street, name, phone1, issue_date,
pl1.notes as work_description,
pl2.notes as work_type
FROM building.permit p
LEFT JOIN public.location l ON p.location_id = l.location_id
LEFT JOIN building.contact_permit cp ON cp.permit_id = p.permit_id
LEFT JOIN public.contact c ON c.contact_id = cp.contact_id
JOIN building.permit_line pl1 ON pl1.permit_id = p.permit_id AND pl1.status = 'A'
JOIN building.item i1 ON pl1.item_id = i1.item_id
  AND i1.description = 'Work Description'
JOIN building.permit_line pl2 ON pl2.permit_id = p.permit_id
  AND pl2.status = 'A'
JOIN building.item i2 ON pl2.item_id = i2.item_id
  AND i2.description ILIKE $1
WHERE street = $2 AND street_no = $3
ORDER BY issue_date DESC NULLS LAST


We get a nice date descending result with null dates at the bottom of the list!.
Yes, this is a real query in one of my apps. Sigh.

No comments:

Post a Comment