ORDERING BEFORE GROUPING?

[aviddv1]

Hey there,

I've got a pretty complex situation for some reason, but I'll try my best to describe it.  I have 3 tables: projects, work orders, and tasks.  Each task is related to a work order and each work order is related to a project.  Each task has a modification timestamp.  I'm trying to return list of all the projects.  At the top of the list would be all the projects with work orders and at the bottom would be projects without work orders.  The first set with work orders would be ordered by the task modification time in descending order so that the project with the work order with the most recently modified task would appear first.  Here's there query I have so far:

[i]select p.project_id, p.project_name, wo.work_order_id, wo.work_order_name, t.task_id, t.task_modified from projects p left join work_orders wo on wo.work_order_project_id=p.project_id left join tasks t on t.task_work_order_id=wo.work_order_id where t.task_user_id=1 and wo.work_order_active=1 and p.project_active=1 order by p.project_name, t.task_modified desc[/i]

I want each project to appear only one time.  I tried adding a group by clause using p.project_id, but grouping before the order means task modification time isn't factored in beforehand.  I'm looking for a way to first order the records then group the results.

You can check out mysql source files here: [url=http://ws.2wsx.ws/tables_dump.zip]http://ws.2wsx.ws/tables_dump.zip[/url].  Any ideas?

Thanks,
Howie

[fenway]

Sounds like you'll need a subquery.

[aviddv1]

that's what I'm thinking.  I just thought maybe there was a more efficient way of doing it.

This is what I have now and somehow it appears to work:

[i] select * from (select p.project_id, p.project_name, p.project_client_id, wo.work_order_id, wo.work_order_name, t.task_id, t.task_modified, if (t.task_user_id=1,1,0) cur_user from projects p left join work_orders wo on wo.work_order_project_id=p.project_id left join tasks t on t.task_work_order_id=wo.work_order_id where p.project_active=1 order by p.project_name, t.task_modified desc) p, users u where p.project_client_id=u.user_client_id  group by p.project_name order by p.cur_user desc, p.task_id, p.work_order_id, p.project_name[/i]

[fenway]

What's unclear to me is how the ordering will persist...

[aviddv1]

I don't know how, but the query I posted will actually list all the projects correctly. The first projects listed are associated with the current user and they are sorted by modification time descending.  Then all of the other projects (ones the user has not modified, but the user's company own's) are sorted by the project name.

So effectively you've got company projects modified by the user ordered by mod time then the rest of the company's projects ordered by the project name.

like I said it's getting so complicated I'm not even sure why I need this functionality.