CODEWITHSUNDEEP
mysqlsqlaggregate
Apemantus
Apemantus

Reputation: 683

SQL Group By query - get related fields of aggregate function

Simplified, but for a table like:

 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)

Upvotes: 2

Views: 2393

Answers (6)

Rahul
Rahul

Reputation: 77896

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)

Upvotes: 0

Yohan Kristianto
Yohan Kristianto

Reputation: 1

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

Upvotes: 0

Devart
Devart

Reputation: 122002

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;

Upvotes: 0

Galz
Galz

Reputation: 6832

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)

Upvotes: 2

Gidon Wise
Gidon Wise

Reputation: 1916

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

Upvotes: 0

reox
reox

Reputation: 5217

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 :))

Upvotes: 0

Related Questions