List:General Discussion« Previous MessageNext Message »
From:Brian Andrews Date:May 3 1999 2:18am
Subject:RE: first and second values in a single row?
View as plain text  
> 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

Thread
first and second values in a single row?chris burgess3 May
  • Re: first and second values in a single row?Sasha Pachev3 May
    • Re: first and second values in a single row?chris burgess3 May
  • RE: first and second values in a single row?Brian Andrews3 May