Selecting 2nd highest value for each unique item

[Mindzai]

I'm having a problem with some SQL. I have a working query which selects the most recent version of each webpage:

SELECT Version.webpage_id, Version.main_text, Version.created, ids.id
FROM cms_versions Version
INNER JOIN (SELECT MAX(id) AS id FROM cms_versions GROUP BY webpage_id) ids ON Version.id = ids.id

However what I need this to do, is select the second most recent version.

Any ideas on how I can achieve this?

Thanks in advance :)

[Mindzai]

I've come up with this which works but it's fugly. There must be a way to get the nth placed item for each group?


SELECT Version.webpage_id, Version.main_text, Version.created, ids.id
FROM cms_versions Version
INNER JOIN
(SELECT MAX(id) AS id FROM cms_versions x WHERE x.id <
(SELECT MAX(id) FROM cms_versions y WHERE x.webpage_id = y.webpage_id)
GROUP BY webpage_id) ids
ON Version.id = ids.id

[paulnic]

how about something like

select max(id) from tablename
where id not in (select max(id) from tablename)

[Mindzai]

Thanks for the reply.

That would get me every row except the latest - I need to divide my data into groups, then get the second most recent item from each group. Getting the nth item overall seems easy enough, it's just the nth within group which is giving me headaches.