Page 1 of 1

grouping problems MySQL

Posted: Tue Aug 29, 2006 5:05 am
by abdabs
i got this table: | ID | refID | date | ... and I want the table sorted by date, but if there are several ID that has the same refID, I want to group them, by picking the ID in the group with the newest date

how can achieve that?

I tried: SELECT ID FROM table GROUP BY refID ORDER BY date DESC.. but that totally twist up my result, it somehow doesn't be sorted by date

Posted: Tue Aug 29, 2006 5:36 am
by volka
You might use subquerries or

Code: Select all

SELECT
	a.ID,a.refID,a.date
FROM
	mytable as a
LEFT JOIN
	mytable as b
ON
	a.refID=b.refID
	AND a.date > b.date
WHERE
	b.date IS Null
to fetch the groupwise minimum.

Posted: Tue Aug 29, 2006 7:53 am
by abdabs
thanks that worked for me.. the example I wrote down is an comment table.. So i connected it together like this:

SELECT * FROM blog,kommentar AS a LEFT JOIN kommentar AS b ON (a.refID = b.refID AND a.dato < b.dato) WHERE b.dato IS NULL AND blog.bID = a.refID ORDER BY a.dato DESC


It sorts the blog's by last comment.. but blog that does not have any comment at all disappears.. how can make them included also? and I might also have ORDER BY a.dato,blog.dato DESC, so I sort the remaining with no comments by the blog's date..

Posted: Tue Aug 29, 2006 5:01 pm
by volka
Does

Code: Select all

SELECT
	*
FROM
	blog
LEFT JOIN
	kommentar AS a
ON
	blog.bID = a.refID
LEFT JOIN
	kommentar AS b
ON
	a.refID = b.refID
	AND a.dato < b.dato
WHERE
	b.dato IS NULL
ORDER
	BY a.dato DESC
solve the problem?

Posted: Wed Aug 30, 2006 5:32 pm
by abdabs
Nope, that only give me the same result.. I would like to avoid using to querys.. There must be a smart solution for this :)

Working on something, but can't really get a solution, argh...

Posted: Thu Aug 31, 2006 4:17 am
by volka
ok, testing time...

Code: Select all

CREATE TABLE `blog` (
  `bID` int(11) unsigned NOT NULL auto_increment,
  `content` varchar(48) NOT NULL,
  PRIMARY KEY  (`bID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `blog` VALUES (1,'blog 1');
INSERT INTO `blog` VALUES (2,'blog 2');
INSERT INTO `blog` VALUES (3,'blog 3');
INSERT INTO `blog` VALUES (4,'blog 4');


CREATE TABLE `kommentar` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `refID` int(11) unsigned NOT NULL default '0',
  `dato` datetime NOT NULL,
  `content` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `kommentar` VALUES (1,3,'2006-08-21 12:00:00','c3');
INSERT INTO `kommentar` VALUES (2,2,'2006-08-21 12:00:00','c2 old');
INSERT INTO `kommentar` VALUES (3,2,'2006-08-24 12:00:00','c2');
INSERT INTO `kommentar` VALUES (4,2,'2006-08-25 12:00:00','c2 new');
INSERT INTO `kommentar` VALUES (5,3,'2006-08-15 12:00:00','c3 old');
Output of the last sql statement is
"bID" "content" "id" "refID" "dato" "content" "id" "refID" "dato" "content"
2 "blog 2" 4 2 "2006-08-25 12:00:00" "c2 new" NULL NULL NULL NULL
3 "blog 3" 1 3 "2006-08-21 12:00:00" "c3" NULL NULL NULL NULL
4 "blog 4" NULL NULL NULL NULL NULL NULL NULL NULL
1 "blog 1" NULL NULL NULL NULL NULL NULL NULL NULL
looks good to me.