MySQL Forums :: Newbie :: Complex Select with Min/Max Syntax

Advanced Search

Complex Select with Min/Max Syntax
Posted by: Dan Rinkes ()
Date: December 02, 2008 04:19PM


I've just recently begun using MySQL and PHP and I'm having some trouble with some syntax for a fairly complex query (to a newbie anyway). Does anyone have a suggestion? I'm using MySQL 5.0.67.

For anyone familiar with the MediaWiki database, that's where this question comes from. I have two tables, one called "revision", and one called "page". Page contains a list of all of the MediaWiki pages and revision contains a list to all edits of all of the pages. In this schema, rev_page is the foreign key in revision relating to page.page_id.



My application needs to list each page, along with the initial editor (which can be determined my using the min() function on the rev_id grouped by rev_page) along with the most recent editor (which can be determined by doing the same procedure with the max() function.

Ideally, the result would look something like this

rev_user_text (min)
rev_user_text (max)

I've been able to get either the min or the max with something like the following

select rv.rev_user_text, rv.rev_page, rv.rev_id
from revision rv,
(select max(rev_id) as maxrev, rev_page
from revision
group by rev_page) results
where (rv.rev_page = results.rev_page
and rv.rev_id = results.maxrev);

but I'm not sure how to join it with the other.

Any help on how to get the other column added would be greatly appreciated.


Edited 1 time(s). Last edit at 12/02/2008 04:22PM by Dan Rinkes.

Options: ReplyQuote

Subject Written By Posted
Complex Select with Min/Max Syntax Dan Rinkes 12/02/2008 04:19PM
Re: Complex Select with Min/Max Syntax Rick James 12/02/2008 11:56PM
Re: Complex Select with Min/Max Syntax Dan Rinkes 12/03/2008 03:57PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.