Hi, there is a table with several trees in nested set form in it.
CREATE TABLE IF NOT EXISTS `posts` ( `id` int(11) unsigned NOT NULL auto_increment, `root_id` int(11) unsigned NOT NULL, `lft` int(11) unsigned NOT NULL, `rgt` int(11) unsigned NOT NULL, `date` datetime NOT NULL, PRIMARY KEY (`id`), KEY `root_id` (`root_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci AUTO_INCREMENT=21 ; I can select all trees and order them by the date-field of their root. SELECT n.*, count(*)+(n.lft>1) AS level FROM posts n, posts p WHERE n.lft BETWEEN p.lft AND p.rgt AND (p.root_id = n.root_id) AND (p.id != n.id OR n.lft = 1) GROUP BY n.id ORDER BY p.date DESC,n.root_id,n.lft Now, i want to select only the 5 most recent trees (the date of a tree is determined by the date of its root). How do I do this elegantly? I could simply select the top 200 rows and truncate the last selected tree (because it will most likely no be completely selected). But then I would not have control over the number of trees (that would not be a big problem, users will learn to live with that). But the big Problem with this solution is, that there is no way to select the next 5/[n] most recent (for the next page for example), since I don't know where the first 200 rows were truncated / in other words, I don't know where the last tree is which was not displayed on the previous page. Another way would be a subquery which only selects the ids of the most recent five roots. That is clean, works great but lacks performance (especially in mysql). Is there another way? Greets, John Smith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
