Page 1 of 1

GROUP BY & ORDER issue

Posted: Sat Jan 17, 2009 4:22 pm
by jaoudestudios
I am trying to get all the unique names out of a table and the relevant date. But I want the most recent date. For some reason I cant get it to work.
My query is...

Code: Select all

 
SELECT name, DATE FROM dialog GROUP BY name ORDER BY id DESC
 
My database structure is...

Code: Select all

 
CREATE TABLE IF NOT EXISTS `dialog` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(40) NOT NULL,
  `message` TEXT NOT NULL,
  `DATE` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `name` (`name`)
) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=46 ;
 
INSERT INTO `dialog` (`id`, `name`, `message`, `DATE`) VALUES
(1, 'eddie', 'aaaaaaaaaa', '2009-01-17 17:56:56'),
(2, 'sara', 'hello', '2009-01-17 17:57:11'),
(3, 'eddie', 'bbbb', '2009-01-17 18:08:32'),
(4, 'eee', 'www', '2009-01-17 18:09:36'),
(5, 'rrr', 'www', '2009-01-17 18:10:17'),
(6, 'yyyyy', 'e', '2009-01-17 18:10:28'),
(7, 'yyyyy', 'wewewew', '2009-01-17 18:22:55'),
(8, 'yyyyy', 'we', '2009-01-17 18:23:42'),
(9, '1111', 'wew', '2009-01-17 18:24:08'),
(10, '1111', 'adf', '2009-01-17 18:24:10'),
(11, '1', 'sd', '2009-01-17 18:30:13'),
(12, '1', 'we', '2009-01-17 18:30:20'),
(13, 'yyyyy', 'www', '2009-01-17 18:30:56'),
(14, 'yyyyy', 't', '2009-01-17 18:30:58'),
(15, 'yyyyy', 'sdf', '2009-01-17 18:31:06'),
(16, 'yyyyy', 'we', '2009-01-17 18:31:09'),
(17, 'yyyyy', 'we', '2009-01-17 18:31:26'),
(18, 'yyyyy', 'we', '2009-01-17 18:31:29'),
(19, 'yyyyy', 'we', '2009-01-17 18:32:33'),
(20, 'yyyyy', 'as', '2009-01-17 18:32:35'),
(21, 'yyyyy', 'ere', '2009-01-17 18:32:38'),
(22, 'yyyyy', 'we', '2009-01-17 18:32:42'),
(23, 'yyyyy', 'werqewrqrqr', '2009-01-17 18:32:47'),
(24, 'yyyyy', 'we', '2009-01-17 18:33:20'),
(25, 'yyyyy', 'wer', '2009-01-17 18:33:23'),
(26, 'yyyyy', '23232', '2009-01-17 18:33:28'),
(27, 'yyyyy', '4545', '2009-01-17 18:33:31'),
(28, 'yyyyy', 'we', '2009-01-17 18:34:45'),
(29, 'yyyyy', '343', '2009-01-17 18:34:47'),
(30, 'yyyyy', 'wer', '2009-01-17 18:35:14'),
(31, 'yyyyy', 'wer', '2009-01-17 18:35:16'),
(32, 'yyyyy', 'asd', '2009-01-17 18:35:18'),
(33, 'yyyyy', 'asdf', '2009-01-17 18:36:12'),
(34, 'yyyyy', 'afd', '2009-01-17 18:36:15'),
(35, 'yyyyy', 'saf', '2009-01-17 18:37:30'),
(36, 'yyyyy', 'sdf', '2009-01-17 18:38:24'),
(37, 'yyyyy', 'wwrw', '2009-01-17 18:40:32'),
(38, 'yyyyy', 'werwrwerwer', '2009-01-17 18:41:39'),
(39, 'yyyyy', 'wer', '2009-01-17 18:47:14'),
(40, '22222', 'asdasf', '2009-01-17 18:53:18'),
(41, '00000', 'werwrwrw', '2009-01-17 19:03:16'),
(42, '00000', 'we', '2009-01-17 19:03:51'),
(43, '00000', 'ad', '2009-01-17 19:03:53'),
(44, '00000', 'yyyy', '2009-01-17 19:03:58'),
(45, 'yyyyy', 'sf', '2009-01-17 20:17:53');
 
Thanks

Re: GROUP BY & ORDER issue

Posted: Sat Jan 17, 2009 4:28 pm
by Burrito
what isn't working?

you could order by date descending and also try a 'SELECT DISTINCT()' instead of grouping by.

Re: GROUP BY & ORDER issue

Posted: Sat Jan 17, 2009 4:40 pm
by jaoudestudios
The query works, but does not return the latest date, it actually returns the first date (oldest).

I tried DISTINCT but it did not do anything - which I thought was v.odd.

EDIT: distinct works if I dont select the date too. But if I select the date I get all results.

Re: GROUP BY & ORDER issue

Posted: Sat Jan 17, 2009 4:44 pm
by VladSun
As I noticed many times, GROUP BY is to be used for aggregating functions, not as a DISTINCT tool (from this point of view, MySQL sux IMHO).
Try MAX(DATE) ;)

Re: GROUP BY & ORDER issue

Posted: Sat Jan 17, 2009 4:47 pm
by Burrito
you're going to need to use `date` and NOT date to tell MySQL that you're referring to your field.

Re: GROUP BY & ORDER issue

Posted: Sat Jan 17, 2009 4:47 pm
by Eran

Code: Select all

SELECT name, MAX(`date`) FROM dialog GROUP BY name ORDER BY id DESC

Re: GROUP BY & ORDER issue

Posted: Sat Jan 17, 2009 4:48 pm
by jaoudestudios
Yep, MAX worked. Thanks :)