I was doing some NASTY SQL coding this evening (or squealing as Smiler would say) and I got bogged down quite a while trying to get DISTINCT and ORDER BY working in the same query. The PostgreSQL error message was not entirely clear and I must have misinterpreted it about 5 times before I finally figured out what it was so desperately trying to tell me!

Turns out that even if you don’t actually want to select the field you are sorting on in a query that uses the DISTINCT keyword you still HAVE to include it in your SELECT list or PSQL will have kittens!

BTW, I am aware that it is considered a ‘bad smell’ by some hard-core SQL programmers to use to DISTINCT key word and I generally agree with their logic (why go to the effort of selecting a load of extra rows that you are just gonna prune off in the end?). However, I can’t for the life of me figure out how to get rid of the DISTINCT in the query below, any insights you may have would be appreciated (looks at Bon)!

SELECT DISTINCT a.id AS assignmentId, a.name AS assignmentName, a.team_id AS teamId, 
  t.name AS teamName, a.completed AS assignmentComp, a.last_updated_at AS lastUpdated
FROM ((student_team st INNER JOIN teams t ON st.team_id = t.id) 
  INNER JOIN assignments a ON t.id = a.team_id) 
  INNER JOIN projects p ON t.project_id=p.id
WHERE a.completed='true'
ORDER BY a.last_updated_at DESC LIMIT 5;