Oops, I meant to copy the list on this reply so that others could
benefit
Rhino
- Original Message -
From: "Rhino" <[EMAIL PROTECTED]>
To: "Mark Phillips" <[EMAIL PROTECTED]>
Sent: Sunday, December 11, 2005 6:19 PM
Subject: Re: Need Help with a query
- Original Message -
From: "Mark Phillips" <[EMAIL PROTECTED]>
To: "MYSQL List"
Sent: Sunday, December 11, 2005 12:38 PM
Subject: Need Help with a query
I have a table with several columns. The ones of interest are flight_id,
angle, and baseline. I want to find the flight_ids for the flights with
the
maximum and minimum altitudes, where
altitude=baseline*tan(radians(angle)).
For example,
Flights
+++---+
| flight_id | angle| baseline |
+++---+
| 1 | 37.0 | 100.0 |
| 2 | 50.0 | 100.0 |
| 3 | 48.0 | 100.0 |
| 4 | 40.0 | 100.0 |
| 5 | 44.0 | 100.0 |
| 6 | 40.0 | 100.0 |
| 7 | 45.0 | 100.0 |
| 8 | 44.0 | 75.0 |
| 9 | 57.8 | 75.0 |
+++---+
The result I am looking for are:
Maximum altitude:
+++
| flight_id | altitude |
+++
| 2 | 119.17536 |
| 9 | 119.17536|
+++
Minimum altitude:
+---+---+
| flight_id | altitudeM |
+---+---+
| 8 | 72.42666 |
+---+---+
Thanks for any help you can provide!
I do wish posters to this list would get in the habit of volunteering
which version of MySQL they are using, particularly for SQL questions!
The answer to almost every SQL question is "it depends on which version of
MySQL you are using". It's very tedious to give the answer for every
version MySQL, as in: If you're using Version 3.x, the answer is A. If
you're using Version 4.0.x the answer is B. If you're using Version 4.1.x,
the answer is C. etc.
[By the way, I don't mean to single you out with this mini-rant; it's just
a general observation.]
Therefore, I'm going to assume you are using Version 4.1 or higher; in
other words, you use a version which supports subqueries. If you are on an
earlier version, please reply to the list and explain which version you
are on. Perhaps someone will be willing to show you alternatives that will
work for you.
I should also explain that I am _not_ on a version of MySQL which supports
subqueries. However, my main database is DB2 which does support subqueries
and the SQL used by DB2 and MySQL is very very similar so this _untested_
answer should be pretty close to what you need.
I think the best answer to your question is to use subqueries. I'm going
to express the answer in pseudocode first to give you a general sense of
the answer, then give you something that should be pretty close to a final
answer that will work on your system.
Pseudocode (for maximum altitude):
select flight_id, baseline*tan(radians(angle)) as max_altitude
from Flights
where baseline*tan(radians(angle)) in (subquery that gets largest altitude
from table)
In real SQL, that should end up looking like this:
select flight_id, baseline*tan(radians(angle)) as max_altitude
from Flights
where baseline*tan(radians(angle)) in (select
max(baseline*tan(radians(angle))) from Flights)
To get the query for the minimum altitude, use the exact same query except
replace the max function with the min function in the subquery and change
the 'as' for the outer query from 'max_altitude' to 'min_altitude'.
For what it's worth, I got slightly different numbers in DB2 so I did not
have a tie for maximum altitude: my maximum altitude was for flight 2 at
119.17535 (versus 119.09797 for flight 9). Perhaps the tan() or radians()
functions in DB2 are slightly more precise?
The 'in' that introduces the subqueries could potentially be replaced by
'=' but 'in' is safer. If you use 'in' and there is more than one flight
tied for the highest altitude, the query works fine. If you use '=' and
there is more than one flight tied for the highest altitude, the query
will almost certainly fail - it does in DB2! - because '=' implies that
only one row in the outer query can have the maximum altitude; therefore
the query fails if more than one row matches.
The most tedious part of these queries is typing the
'baseline*tan(radians(angle))' expression. You might expect that there
would be some shortcut that would enable you to type the expression only
once and then make repeated references to it. Sometimes that is possible
but without a suitable version of MySQL handy, I don't want to propose any
possible ways to do that since I can't be sure they'll work. Perhaps
people with newer versions of MySQL can suggest shortcuts that reduce the
amount of typing.
Rhino
--
