***BOGO*** Sorted GROUP BY

["Johan Solve" <johan.solve@xxxxxxxxx>]

This is a classic problem. We have these tables (full table dump further down):
article (id, title)
textblock (id, article_id, text, image_url, sort (and a bunch of more
data fields))

For each article I want to show only the first related textblock,
according to the sort field in the textblock table.

Each article can have zero or more textblocks.


This query gives me one textblock for each article, but not
necessarily the first one:
SELECT article.*, textblock.text, textblock.image_url
FROM article
LEFT JOIN textblock ON textblock.article_id=article.id
GROUP BY article.id

Doing a SORT BY t.sort before the GROUP BY does not work.


I could do a subquery that pulls the first textblock for each article
and returns a single column from the textblock table, but I need
several textblock columns so that's an impractical solution.

I think a solution would involve subqueries (possibly for the FROM statement).

Here's one solution I've come up with, but I would need help to figure
out how efficient it is:
SELECT a.*,  t.*, (SELECT MIN(sort) AS minsort FROM textblock t_sub
WHERE t_sub.article_id=a.id GROUP BY article_id) minsort
FROM article a
LEFT JOIN textblock t ON a.id = t.article_id
HAVING t.sort=minsort OR t.id IS NULL


Table dump follows:
CREATE TABLE `article` (
  `id` int(20) unsigned NOT NULL auto_increment,
  `title` varchar(255) collate utf8_swedish_ci NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

INSERT INTO `article` (`id`,`title`) VALUES ('1','Scope of today');
INSERT INTO `article` (`id`,`title`) VALUES ('2','Another scope');
INSERT INTO `article` (`id`,`title`) VALUES ('3','News draught');


CREATE TABLE `textblock` (
  `id` int(20) unsigned NOT NULL auto_increment,
  `article_id` int(10) unsigned NOT NULL,
  `text` text collate utf8_swedish_ci NOT NULL,
  `image_url` varchar(255) collate utf8_swedish_ci NOT NULL,
  `sort` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `article_id` (`article_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

INSERT INTO `textblock` (`article_id`,`text`,`image_url`,`sort`)
VALUES ('1','This is text block 1','picture1.png','50');
INSERT INTO `textblock` (`article_id`,`text`,`image_url`,`sort`)
VALUES ('1','This is text block 2','picture2.png','20');
INSERT INTO `textblock` (`article_id`,`text`,`image_url`,`sort`)
VALUES ('1','This is text block 4','picture3.png','30');
INSERT INTO `textblock` (`article_id`,`text`,`image_url`,`sort`)
VALUES ('2','This is another text block 1','picture5.png','30');
INSERT INTO `textblock` (`article_id`,`text`,`image_url`,`sort`)
VALUES ('2','This is another text block 2','picture6.png','40');
INSERT INTO `textblock` (`article_id`,`text`,`image_url`,`sort`)
VALUES ('2','This is another text block 4','picture7.png','10');
INSERT INTO `textblock` (`article_id`,`text`,`image_url`,`sort`)
VALUES ('2','This is another text block 5','picture8.png','20');



-- 
Mvh
Johan Sölve
____________________________________
Montania System AB
Halmstad Stockholm Malmö
http://www.montania.se

Johan Sölve
Mobil +46 709-51 55 70
johan@xxxxxxxxxxx

Kristinebergsvägen 17, S-302 41 Halmstad, Sweden
Telefon +46 35-136800 | Fax +46 35-136801

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx