aviddv1 Posted December 16, 2006 Share Posted December 16, 2006 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 Link to comment https://forums.phpfreaks.com/topic/30885-ordering-before-grouping/ Share on other sites More sharing options...
fenway Posted December 16, 2006 Share Posted December 16, 2006 Sounds like you'll need a subquery. Link to comment https://forums.phpfreaks.com/topic/30885-ordering-before-grouping/#findComment-142510 Share on other sites More sharing options...
aviddv1 Posted December 16, 2006 Author Share Posted December 16, 2006 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] Link to comment https://forums.phpfreaks.com/topic/30885-ordering-before-grouping/#findComment-142516 Share on other sites More sharing options...
fenway Posted December 16, 2006 Share Posted December 16, 2006 What's unclear to me is how the ordering will persist... Link to comment https://forums.phpfreaks.com/topic/30885-ordering-before-grouping/#findComment-142519 Share on other sites More sharing options...
aviddv1 Posted December 16, 2006 Author Share Posted December 16, 2006 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. Link to comment https://forums.phpfreaks.com/topic/30885-ordering-before-grouping/#findComment-142523 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.