SQL Group By query - get related fields of aggregate function

Simplified, but for a table li开发者_如何学运维ke:

 id time distance price
 1  20   500      8 
 2  50   500      10 
 3  90   500      12 
 4  80   1000     17 
 5  170  1000     11 
 6  180  1000     13 
 7  19   800      12 

I want to get the rows with the quickest time for the distances 500 and 1000, i.e.

 id time distance price
 1  20   500      8 
 4  80   1000     17 

If I do

select min(time) from table

that works fine for finding the price, but I can't get the id and price - only the max/min/average/first value of all ids/prices.

I can do it with multiple look ups - e.g.

select * from table where distance = 500 and time = 20 
select * from table where distance = 1000 and time = 80 

but is there a better way that doesn't involve 1 + (number of distances) queries (or at least provides one resultset, even if internally it uses that number of queries)

You will need to use an inner select:

SELECT t.id, t.time, t.distance, t.price
FROM table t
JOIN (SELECT MIN(time) as min_time, distance
        FROM table
        GROUP BY distance) as tmp
      ON (t.distance = tmp.distance AND t.time = tmp.min_time)
WHERE t.distance IN (500, 1000)

you need to put the min stuff into a having clause, so your query would be select * from table group by distance having min(distance); (untested) or you could use subquerys to find that out: select * from table where distance = (select distance from table where min(time)) and time = select min(time) from table) (also untested :))

just order by and limit. then you have the fastest for the 500 distance in 1 query.

select * from thetable where distance = 500 ORDER BY time ASC LIMIT 1

Try this one -

SELECT t1.* FROM table1 t1
  JOIN (SELECT distance, MIN(time) min_time FROM table11 WHERE distance = 500 OR distance = 1000 GROUP BY distance) t2
    ON t1.distance = t2.distance AND t1.time = t2.min_time;

SELECT * FROM tblData INNER JOIN (SELECT MIN(TIME) AS minTime, distance FROM tblData WHERE distance IN (500,1000) GROUP BY distance) AS subQuery ON tblData.distance = subQuery.distance AND tblData.time = subQuery.minTime

What about this one ... gies exactly what you are looking for (TESTED)

select *  from Table1 where time in (select min(time)  from table1 where distance = 500 or distance = 1000 group by distance) and (distance = 500 or distance = 1000)