March 21st, 2013, 07:52 AM
Add field to query
I need to add a field to a query that someone kindly helped me with yesterday:
I have added a field called operator (integer) but when I run the query I now get multiple rows for accounts where I need to show the last row.
For example I want to show the highlighted riw:
acct1 operator1 01/01/2011
acct1 operator2 01/03/2013
Here is the query that I amended - unsuccesfully!
SELECT account, operator, keyfield, note_date, note_note, note_type, filter
WHERE ((account::text, keyfield) IN ( SELECT account, max(keyfield) AS max
WHERE filter::text = 'OB'::text AND note_date >= '2010-01-01 00:00:00+00'::timestamp with time zone AND (note_type::text = ANY (ARRAY['1'::character varying, '2'::character varying, '3'::character varying, '4'::character varying, '5'::character varying]::text))
GROUP BY account, operator))
ORDER BY account ASC;
March 21st, 2013, 08:39 AM
the GROUP BY clause is wrong. When you group by both account and operator, each combination of those two values form a separate group. So you get the latest row for each (account, operator) rather than for each account.
Remove the "operator" from the GROUP BY clause, and you're done.
March 21st, 2013, 08:53 AM
Thanks yet again for great help!