title: Informix subqueries - how to execute them properly
body: <p>I'm working on an Informix query that wiil 1) Give me a list of call center agents and 2) give me their most recent status.  I have a query that does almost everything I need;</p>
  
  <pre><code>select b.resourcename, b.extension, a.eventtype
  from agentstatedetail as a, resource as b, team as c
  where date (eventdatetime) = TODAY
    and (a.agentid = b.resourceid)
    and (b.assignedteamid = c.teamid)
    and (c.teamname like 'Team Name %')
  group by b.resourcename, b.extension, a.eventtype
  order by resourcename asc
  </code></pre>
  
  <p>However, this will give me a complete record of "eventtype" (eventtype shows the states an agent was in at a given time).  I'm trying to pull only the most recent state.  So, I believe I need to do a subquery to this main query stating something like, "If the main query's output is found in this subquery, then keep it".  The subquery looks like this;</p>
  
  <pre><code>select a.agentid, b.resourcename, c.teamname, max(a.eventdatetime)
  from agentstatedetail as a, resource as b, team as c
  where date (eventdatetime) = TODAY
    and a.agentid = b.resourceid
    and b.assignedteamid = c.teamid
    and c.teamname like 'Team Name %'
  group by a.agentid, b.resourcename, c.teamname
  </code></pre>
  
  <p>This subquery, by itself, is almost everything I need, but it doesn't have the eventtype.  How would this query work?</p>
  
  <p>This is the format that I typically write subqueries in but can't make work in Informix;</p>
  
  <pre><code>select a.agentid, b.resourcename, c.teamname, max(a.eventdatetime) as eventdatetime, a.eventtype from agentstatedetail as a, resource as b, team as c where date(eventdatetime) = TODAY and a.agentid=b.resourceid and b.assignedteamid=c.teamid and c.teamname like 'ITS %' and where a.eventdatetime in (select a.agentid, b.resourcename, c.teamname, max(a.eventdatetime) from agentstatedetail as a, resource as b, team as c where date(eventdatetime) = TODAY and a.agentid=b.resourceid and b.assignedteamid=c.teamid and c.teamname like 'Team Name %' group by a.agentid, b.resourcename, c.teamname) group by a.agentid, b.resourcename, c.teamname, a.eventtype order by max(a.eventdatetime) desc
  </code></pre>