GROUP BY & ORDER issue
Posted: Sat Jan 17, 2009 4:22 pm
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...
My database structure is...
Thanks
My query is...
Code: Select all
SELECT name, DATE FROM dialog GROUP BY name ORDER BY id DESC
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');