grouping problems MySQL

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
abdabs
Forum Newbie
Posts: 7
Joined: Tue Aug 08, 2006 6:26 am

grouping problems MySQL

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
abdabs
Forum Newbie
Posts: 7
Joined: Tue Aug 08, 2006 6:26 am

Post 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..
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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?
abdabs
Forum Newbie
Posts: 7
Joined: Tue Aug 08, 2006 6:26 am

Post 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...
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
Post Reply