New answers tagged

0

You don't need the extra layer SELECT * FROM (...), do this ( SELECT ... ) -- without any ORDER BY UNION ( SELECT ... ) -- without any ORDER BY UNION ... UNION ( SELECT ... ) -- without any ORDER BY ORDER BY ... Because of the parentheses, it is clear that the ORDER BY belongs to the result of the UNION, not the final SELECT. It is usually 'wrong' to ...


0

The outer SELECT * FROM does not have to respect the ORDER BY in the inner query. Other RDBMSes will actually error in this situation, as an ORDER BY in subqueries is deemed invalid/not necessary. To fix, alter your query so it's of the form: SELECT * FROM (SELECT sale.name as "Name", -- rest of subquery ORDER BY account.close_date desc) ...


1

Looks like you are sorting by close_date column in all the tables in DESC order then UNION. Instead of this approach SELECT UNION all the tables and, in the final result you can ORDER BY the Close Date:data:200 as DESC. The working query will be: SELECT T.* FROM ( SELECT sale.NAME AS "Name" ,sale.responsible AS "Responsible:data:200" ,...


1

For the simple result (just the id), EXCEPT might be simplest and fastest: Also, since you write: I get their primary keys in an array Simply use unnest() and don't waste time joining to the contacts table: SELECT id FROM unnest('{1,10,20,1557,5000,15057}'::int[]) id -- actual array (not list) EXCEPT ALL SELECT contact_id FROM case_contacts EXCEPT ...


2

You may need to alter this slightly for postgres (is is MS SQL syntax) but something like: SELECT id FROM contacts LEFT OUTER JOIN FROM case_contacts ON case_contacts.contact_id = contacts.id LEFT OUTER JOIN FROM case_payments ON case_payments.contact_id = contacts.id LEFT OUTER JOIN FROM invoices ON invoices.contact_id = contacts.id WHERE ...


0

Yes, there is a less redundant/verbose way -- PARTITION a single table by year. Then a single SELECT will do the job. But, that may not even be wise. What queries benefit from your splitting it into multiple tables?



Top 50 recent answers are included