Retrieve first result of LEFT OUTER JOIN

I have this query to retrieve all main pages and sub pages of that page. I use this query and 开发者_StackOverflow中文版then seperate the pages later by checking if the page has a parent_id.

(the difference between a main page and a sub page is that the main page has a parent_id of 0 and a sub page > 0)

SELECT name, url_name, parent_id
FROM pages WHERE active = '1' AND visible = '1' AND deleted = '0'
AND ( parent_id = 0 OR parent_id = " . $main_page['id'] . ")
ORDER BY order_number DESC, id ASC

Now I need to get the FIRST sub page (by ordering by order_number) for every main page.

How would I do this?

Thanks in advance.

SELECT * FROM pages 
WHERE id IN (
    SELECT max(id) FROM pages
    WHERE aive = '1' AND visible = '1' AND deleted = '0'
    GROUP BY parent_id
)

try something like :

SELECT p1.name, p1.url_name, p1.parent_id, p2.id, p2.name, p2.url_name
FROM pages p1
LEFT OUTER JOIN pages p2
ON p2.parent_id = p1.id
WHERE p1.parent_id = 0
GROUP BY p1.id
ORDER BY p2.order_number ASC, p1.id ASC

This is best solved in MySQL as an exclusion join. This is very similar to many questions answered under the greatest-n-per-group tag on StackOverflow.

SELECT p.name, p.url_name
FROM pages p
LEFT OUTER JOIN pages p2 ON p.order_number > p2.order_number
  AND (p2.parent_id, p2.active, p2.visible, p2.deleted) = ({$main_page["id"], 1, 1, 0) 
WHERE (p.parent_id, p.active, p.visible, p.deleted) = ({$main_page["id"]}, 1, 1, 0)
AND p2.id IS NULL;

This solution looks for a row p2 that has a lower order_id and otherwise matches the same conditions as p. If no such lower-ordered row is found, then p must have the lowest order_id of those in the same group that matches the condition, i.e. those with the same parent_id.

If you have a compound index on (parent_id, active, visible, deleted, order_id), this should be a very efficient query.

One part of this solution that you may not expect is that you need to repeat the conditions inside the left join criteria.

I edited the query above after testing it. It should work better now. Note that this is not comparing the order_number of the subpage to its parent's order_number -- it's comparing the order_number of a subpage to that of its siblings, i.e. subpages of the same parent.