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.
Subject
Written By
Posted
Complex Select with Min/Max Syntax
December 02, 2008 04:19PM
December 02, 2008 11:56PM
December 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.