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

Hi,

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.

revision
--------
rev_page
rev_id
rev_timestamp
rev_user_text

page
----
page_id
page_title
page_counter

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

page_id
page_title
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.

Thanks,
Dan



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.