Latest owner query

[xsist10]

Hey guys

I have 2 tables in my system that allow me to trace requests from clients.
One table contains the information about the client and the other contains the ownership of the query. This allows the clients "query" to be passed back and forth between various peole inside the company.

Table information below (culled some fields for simplification)
[code]
CREATE TABLE  `query` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(200) NOT NULL default '',
  `query` text NOT NULL,
  `created` datetime default NULL,
  `completed` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
);

CREATE TABLE  `query_trace` (
  `id` int(11) NOT NULL auto_increment,
  # corresponds to the entry in query table
  `query_id` int(11) NOT NULL default '0',
  `created` datetime default NULL,
  `completed` datetime NOT NULL default '0000-00-00 00:00:00',
  # the staff member this query has been passed to
  `owner` varchar(60) NOT NULL default '',
  PRIMARY KEY  (`id`)
);
[/code]

I want to query "query_trace" for a list of query_id's that belong to a specific owner only if it is the latest (created >) uncompleted (completed != "0000-00-00 00:00:00) entry.

Using GROUP BY query_id leaves me with the first entry for a specific query_id. I need to perform some kind of ORDER on the query before I cull un-needed entries.

[Vikas Jayna]

[code]select query_id from query_trace where owner='xyz' and completed != "0000-00-00 00:00:00" order by created desc limit 1[/code]

[xsist10]

I need a list of all the client queries belonging to a staff member, not just the first.

[fenway]

Try this (untested):

[code]
SELECT query_id
FROM query_trace
WHERE ( owner, created ) = ( SELECT owner, MAX( created ) FROM query_trace WHERE completed != "0000-00-00 00:00:00" GROUP BY owner )
[/code]

[xsist10]

Thanks man. The query was a little buggy but I had completely forgotten about the MAX command. Made a similar query which sorted it out.  ;D