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.