Databases
StackOverflow2013
Postsdbo
PONested query to find details in table B for maximum value in table A
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONested query to find details in table B for maximum value in table A
    primarykey
    data
    text
    <p>I've got a huge bunch of <code>flights</code> travelling between <code>airports</code>. </p> <p>Each airport has an ID and (x,y) coordinates. </p> <p>For a given list of flights belonging to a user, I want to find the northernmost (highest y) airport visited. </p> <hr> <p>Here's the query I'm currently using:</p> <pre><code>SELECT name,iata,icao,apid,x,y FROM airports WHERE y=(SELECT MAX(y) FROM airports AS a , flights AS f WHERE (f.src_apid=a.apid OR f.dst_apid=a.apid) AND f.uid=[user_id] ) </code></pre> <p>This works beautifully and reasonably fast as long as <code>y</code> is unique (= there's only one airport at that latitude), but fails once it isn't. Unfortunately this happens quite often, as eg. military and civilian airports have separate entries even though they occupy the same coordinates.</p> <p>What I'd really want to do is find the airport with <code>MAX(y)</code> in the subquery and return the actual matching airport (<code>a.apid</code>), instead of returning the value of <code>y</code> and then matching it again. Any suggestions?</p> <hr> <p>Assume the user has only this one flight, from apid '3728':</p> <pre><code>mysql&gt; select * from flights where uid=35 and src_apid=3728 limit 1; +------+----------+----------+----------+----------+------+------+-----------+-------+--------+------+------+------+--------+----------+--------------+--------------+---------------------+------+------------+------+ | uid | src_apid | src_time | dst_apid | distance | code | seat | seat_type | class | reason | plid | alid | trid | fid | duration | registration | note | upd_time | opp | src_date | mode | +------+----------+----------+----------+----------+------+------+-----------+-------+--------+------+------+------+--------+----------+--------------+--------------+---------------------+------+------------+------+ | 35 | 3728 | NULL | 3992 | 4116 | NW16 | 23C | A | Y | L | 167 | 3731 | NULL | 107493 | 08:00:00 | | del. typhoon | 2008-10-04 10:40:58 | Y | 2001-08-22 | F | +------+----------+----------+----------+----------+------+------+-----------+-------+--------+------+------+------+--------+----------+--------------+--------------+---------------------+------+------------+------+ </code></pre> <p>And there are two airports at the same coordinates:</p> <pre><code>mysql&gt; select * from airports where y=21.318681; +-----------------------+----------+---------------+------+------+-------------+-----------+-----------+------+------+----------+------+ | name | city | country | iata | icao | x | y | elevation | apid | uid | timezone | dst | +-----------------------+----------+---------------+------+------+-------------+-----------+-----------+------+------+----------+------+ | Honolulu Intl | Honolulu | United States | HNL | PHNL | -157.922428 | 21.318681 | 13 | 3728 | NULL | -10 | N | | Hickam Air Force Base | Honolulu | United States | | PHIK | -157.922428 | 21.318681 | 13 | 7055 | 3 | -10 | N | +-----------------------+----------+---------------+------+------+-------------+-----------+-----------+------+------+----------+------+ </code></pre> <p>If you run the original query, the subquery will return y=21.318681, which in turn will match either apid 3728 (correct) <em>or</em> apid 7055 (wrong).</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjpatokal
    1. USjpatokal
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PONested query to find details in table B for maximum value in table A
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PONested query to find details in table B for maximum value in table A
      1. USjpatokal
      1. VTBountyStart
    3. VO
      singulars
      1. PONested query to find details in table B for maximum value in table A
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you give a few example rows in airports and flights that shows the problem along with the expected resultset? I'd be happy to help but I don't understand the issue clearly enough.
      singulars
      1. PONested query to find details in table B for maximum value in table A
      1. USRonnis
    2. COThe query should be modified to only return airports that the passenger has visited, i.e. in the sample data shown the passenger visited 3728 and 3992, so only one of those airports should be returned, thus eliminated Hickam (apid=7055).
      singulars
      1. PONested query to find details in table B for maximum value in table A
      1. USBob Jarvis
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload