Skip to content

Instantly share code, notes, and snippets.

@kballenegger
Last active August 29, 2015 14:05
Show Gist options
  • Save kballenegger/7e1e1498bab1a50eac8c to your computer and use it in GitHub Desktop.
Save kballenegger/7e1e1498bab1a50eac8c to your computer and use it in GitHub Desktop.
Postgres select by most recently used in relation
SELECT payees.*, max(payments.id) AS most_recent_transaction FROM payees
LEFT OUTER JOIN payments ON payments.payee_id = payees.id
WHERE payees.user_id = ?
GROUP BY payees.id
ORDER BY max(payments.id) DESC;
@kballenegger
Copy link
Author

My error: (with different table names)

x=# select bill_payees.*, max(check_transactions.id) from bill_payees
left outer join check_transactions on check_transactions.payee_id = bill_payees.id
where bill_payees.user_id = 927244602504659414
group by bill_payees.id
order by check_transactions.id desc;
ERROR:  column "check_transactions.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 5: order by check_transactions.id desc;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment