Subject RE: [firebird-support] SQL Help please: Group by with a text blob
Author Sasha Matijasic
> I've been using a normal select with no joins, then I needed to pull in
> another value MAX(.....). The problem is that I'm selecting a text blob
> which cannot go in the 'Group by', Is there a way to rewrite the SQL
> below
> so that I can still pull in the MAX col and also the Text blob?
> SELECT t.TASK_ID, t.TITLE, t.DESCRIPTION <lots of other t. fields here>
> , MAX(th.change_id) as LastViewChangeID
> from TASKS t
> left outer join TASKS_HISTORY th
> on t.TASK_ID = th.TASK_ID and th.CHANGE_TYPE = 'V' and
> th.created_by_ID=:USER_ID
> group by t.TASK_ID, t.TITLE, t.DESCRIPTION <other t. fields>

Something like this?

select t.task_id, t.title, t....,
(select max(change_id) from task_history
where task_id = tasks.task_id nad change_type = 'V' and created_by_id = :user_id
) lastviewchangeid
from tasks
group by...