[SOLVED] Help with MySQL query using both min() and max()

[danrinkes]

Hi,

 

I've just recently begun using MySQL and PHP and I'm having some trouble with some syntax for a fairly complex query (complex to me 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 name (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.  The group by clause prevents me from aliasing the result table and joining it with another, I think.

 

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

 

Thanks,

Dan

[fenway]

You have a timestamp... why use the id?

[danrinkes]

You're right, I could use timestamp also.  My thinking was that could possibly (though not likely) be multiple pages that could have the same timestamp.  The revision ID will always be unique.

 

At any rate, I found the solution on a different forum.  I'll post here for anyone that is interested.  The simplest solution was to forego using Min() and Max() and use a LIMIT 1 clause.

 

SELECT page_id, page_title,

    (SELECT rev_user_text from revision revmin

      WHERE revmin.rev_page = page_id

      ORDER BY rev_id ASC LIMIT 1) as init_editor,

    (SELECT rev_user_text from revision revmax

      WHERE revmax.rev_page = page_id

      ORDER BY rev_id DESC LIMIT 1) as last_editor

FROM page

 

Thanks,

Dan

 

[fenway]

I was thinking that any update to a revision would change the timestamp but not the uid.

 

Anyway, you could still do it with max/min, but you'd have to join them back to the main table to get the details on eacah page.