GROUP BY & ORDER issue

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

GROUP BY & ORDER issue

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: GROUP BY & ORDER issue

Post by Burrito »

what isn't working?

you could order by date descending and also try a 'SELECT DISTINCT()' instead of grouping by.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: GROUP BY & ORDER issue

Post 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.
Last edited by jaoudestudios on Sat Jan 17, 2009 4:46 pm, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: GROUP BY & ORDER issue

Post 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) ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: GROUP BY & ORDER issue

Post by Burrito »

you're going to need to use `date` and NOT date to tell MySQL that you're referring to your field.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: GROUP BY & ORDER issue

Post by Eran »

Code: Select all

SELECT name, MAX(`date`) FROM dialog GROUP BY name ORDER BY id DESC
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: GROUP BY & ORDER issue

Post by jaoudestudios »

Yep, MAX worked. Thanks :)
Post Reply