I forgot to copy the list as well.... Mark
---------- Forwarded Message ---------- Subject: Re: Need Help with a query Date: Sunday 11 December 2005 06:47 pm From: Mark Phillips <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Rhino, My apologies for leaving out the version of mysql. I agree 1000% with your rant - it was an oversight on my part. I have mysql 4.0.24 on my development machine and 4.1.11 on my production machine. Thanks for the query - it works very well in 4.1.11. I think in 4.0.24 I need to use a temporary table. Thanks again! Mark On Sunday 11 December 2005 04:19 pm, you wrote: > ----- Original Message ----- > From: "Mark Phillips" <[EMAIL PROTECTED]> > To: "MYSQL List" <mysql@lists.mysql.com> > 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.00000 | 100.00000 | > > | 2 | 50.00000 | 100.00000 | > > | 3 | 48.00000 | 100.00000 | > > | 4 | 40.00000 | 100.00000 | > > | 5 | 44.00000 | 100.00000 | > > | 6 | 40.00000 | 100.00000 | > > | 7 | 45.00000 | 100.00000 | > > | 8 | 44.00000 | 75.00000 | > > | 9 | 57.80000 | 75.00000 | > > > > +------------+----------------+---------------+ > > > > 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 -- Mark Phillips [EMAIL PROTECTED] 602 524-0376 ------------------------------------------------------- -- Mark Phillips [EMAIL PROTECTED] 602 524-0376 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
