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
grouping problems MySQL
Moderator: General Moderators
You might use subquerries orto fetch the groupwise minimum.
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 Nullthanks 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..
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..
Doessolve the problem?
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 DESCok, testing time...
Output of the last sql statement is
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');looks good to me."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