> i need to get the highest and second highest values in a column,
> preferably in the same query. i think this could be done with a
> VIEW by combining the two queries, but i'd like to know if it can be
> done in MySQL.
>
> i want to return the highest value of X, and a value calculated off
> the second highest value of X. for example, the last time a safety
> check was made, and the time one week after the previous check, but
> the date aspect is unimportant. i'd like to get them in as this_X and
> last_X in a single row.
>
> at the same time i'll be retrieving other information from the row
> this_X lives in, and getting data from other JOINed tables. the only
> thing i need from last_X row is last_X.
>
> can this be done with a single response row answer?
Hey fellow Kiwi :),
Try something like:
select max(x.Myfield), max(y.Myfield) from Mytable as x, Mytable as y where
y.Myfield < x.Myfield ;
cheers
Brian
+---------------------- I n f o r M a t e ------------------------+
| Brian Andrews Web Design, Web Hosting +64 9 479-7876 |
| Director Software Development +64 21 OFFICE |
| brian@stripped PO Box 33-863 Takapuna |
| http://www.InforMate.co.nz/ Auckland, New Zealand |
+-----------------------------------------------------------------+
Where am I? Here:
http://www.expediamaps.com/default.asp?C=-36.7343250087852,174.748856169468&
A=150&OX=0&OY=0&E=P9a462c
