Geek Answers Handbook

One sql query to execute some group

CREATE TABLE IF NOT EXISTS `projects` ( `idproject` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `date` datetime NOT NULL, `status` enum('new','active','closed') NOT NULL, `priority` enum('low','medium','high') NOT NULL, PRIMARY KEY (`idproject`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=20

Here are some details:

  INSERT INTO `projects` (`idproject`, `name`, `date`, `status`, `priority`) VALUES (1, 'iCompany', '2011-03-23 11:41:44', 'new', 'medium'), (2, 'John Doe & Co.', '2011-04-09 14:38:04', 'closed', 'low'), (3, 'ACME, Inc.', '2011-05-21 11:43:11', 'active', 'high'), (4, 'John Doe & Co.', '2011-03-28 15:19:45', 'active', 'low'), (5, 'John Doe & Co.', '2011-03-08 15:16:32', 'new', 'low'), (6, 'ACME, Inc.', '2011-04-05 20:58:42', 'active', 'low'), (7, 'Mega Corp', '2011-04-21 08:08:53', 'new', 'low'), (8, 'iCompany', '2011-04-17 08:40:36', 'active', 'medium'), (9, 'iCompany', '2011-05-18 14:36:48', 'active', 'low'), (10, 'John Doe & Co.', '2011-04-18 19:08:25', 'new', 'medium'), (11, 'ACME, Inc.', '2011-05-19 13:11:04', 'active', 'low'), (12, 'Foo Bars', '2011-03-03 17:19:29', 'new', 'high'), (13, 'ACME, Inc.', '2011-04-23 20:42:33', 'active', 'medium'), (14, 'Foo Bars', '2011-05-13 09:18:15', 'active', 'medium'), (15, 'ACME, Inc.', '2011-03-20 14:37:18', 'new', 'low'), (16, 'Foo Bars', '2011-04-18 13:46:23', 'active', 'high'), (17, 'iCompany', '2011-05-31 07:13:32', 'closed', 'low'), (18, 'Foo Bars', '2011-05-31 15:43:39', 'active', 'low'), (19, 'John Doe & Co.', '2011-05-28 11:28:32', 'active', 'medium')

I would like to have a list of all projects: - with their last (chronological) status and priority, - with the number of days between the first and last record (0, if there is only one record), you can ignore the clock, - sorted by priority (first " high "), then by name, - without projects where the last status is" closed "(excluded from the result).

The conclusion should be:

 +---------------+-----------+---------------+----------------+ ¦name ¦total_days ¦latest_status ¦latest_priority ¦ +---------------+-----------+---------------+----------------+ ¦ACME, Inc. ¦62 ¦active ¦high ¦ ¦John Doe & Co. ¦81 ¦active ¦medium ¦ ¦Foo Bars ¦89 ¦active ¦low ¦ ¦Mega Corp ¦0 ¦new ¦low ¦ +---------------+-----------+---------------+----------------+

So far, I had to write this:

  SELECT name,status FROM projects group by name order by priority desc,name

please, help?

+7
source share
2 answers
 SELECT * FROM ( SELECT name, DATEDIFF(MAX(date), MIN(date)) total_days, (SELECT tt.status FROM projects tt WHERE t.name = tt.name AND tt.date = MAX(t.DATE)) latest_status, (SELECT tt.priority FROM projects tt WHERE t.name = tt.name AND tt.date = MAX(t.DATE)) latest_priority FROM projects t GROUP BY name ) t WHERE latest_status != 'closed' ORDER BY (CASE latest_priority WHEN 'high' THEN 0 WHEN 'medium' THEN 1 WHEN 'low' THEN 2 END), name;
  • common day : take DATEDIFF dates MAX and MIN , which will indicate the number of days between them;
  • last status : select the status for which the row date is equal to the MAX date;
  • last priority : select the priority for which the row date is equal to the MAX date;
  • order : translate each priority line into a numerical value and order.

Here is sqlfiddle .

+4
source

Try the following: http://www.sqlfiddle.com/#!2/b3962/1

 select p.name, r.total_days, p.status as latest_status, p.priority as latest_priority from projects p join ( select name, max(date) as recent_date, datediff(max(date),min(date)) as total_days from projects group by name ) -- recent r on(r.name,r.recent_date) = (p.name,date) where p.status not in ('closed') order by (case latest_priority when 'high' then 0 when 'medium' then 1 when 'low' THEN 2 end), p.name

Output:

 | NAME | TOTAL_DAYS | LATEST_STATUS | LATEST_PRIORITY | ----------------------------------------------------------------- | ACME, Inc. | 62 | active | high | | John Doe & Co. | 81 | active | medium | | Foo Bars | 89 | active | low | | Mega Corp | 0 | new | low |

If you want to make it shorter (using USING ), make the alias recent_date in date : http://www.sqlfiddle.com/#!2/b3962/2

 select p.name, r.total_days, p.status as latest_status, p.priority as latest_priority from projects p join ( select name, max(date) as date, datediff(max(date),min(date)) as total_days from projects group by name ) r using(name,date) where p.status not in ('closed') order by (case latest_priority when 'high' then 0 when 'medium' then 1 when 'low' then 2 end), p.name

How it works

Work inside out. First step, find the latest version:

  select name, max(date) as recent_date, datediff(max(date),min(date)) as total_days from projects group by name

Output:

 | NAME | DATE | TOTAL_DAYS | -------------------------------------------------------------- | ACME, Inc. | May, 21 2011 11:43:11-0700 | 62 | | Foo Bars | May, 31 2011 15:43:39-0700 | 89 | | iCompany | May, 31 2011 07:13:32-0700 | 69 | | John Doe & Co. | May, 28 2011 11:28:32-0700 | 81 | | Mega Corp | April, 21 2011 08:08:53-0700 | 0 |

Final step, combine the above results with the main table:

 select p.name, r.total_days, p.status as latest_status, p.priority as latest_priority from projects p join ( select name, max(date) as date, datediff(max(date),min(date)) as total_days from projects group by name ) r using(name,date) where p.status not in ('closed') order by (case latest_priority when 'high' then 0 when 'medium' then 1 when 'low' then 2 end), p.name

To override the order, use the CASE statement in the ORDER BY clause.

Output:

 | NAME | TOTAL_DAYS | LATEST_STATUS | LATEST_PRIORITY | ----------------------------------------------------------------- | ACME, Inc. | 62 | active | high | | John Doe & Co. | 81 | active | medium | | Foo Bars | 89 | active | low | | Mega Corp | 0 | new | low |
+4
source

More articles:


All Articles