CODEWITHSUNDEEP
mysql
Mr Asker
Mr Asker

Reputation: 2380

Get the record with the min distance

I am trying to get the record with the min distance with this query below but I am no getting anything back. The query is being executed but the result is empty. How can I get the record with the mini distance?

query:

SELECT mac, stop_name, stop_distance, speed 
FROM behaviour 
WHERE stop_distance = (SELECT MIN(stop_distance) 
                       FROM behaviour)
AND mac = '10:A5:D0:06:C6:E9' 
AND stop_name = 'Stadthalle'
LIMIT 1

Scrennshot:

enter image description here

Upvotes: 0

Views: 43

Answers (2)

Jean-François Savard
Jean-François Savard

Reputation: 21004

You get no result because the minimum distance returned by the subquery is not filtered with the filter you give to the parent query.

Thus the min distance is 60, but there is no rows that match this speed having at the same time

  • mac = ' 10:A5:D0:06:C6:E9'
  • stop_name = 'Stadthalle'

You should instead add the filters into the subquery :

SELECT mac, stop_name, stop_distance, speed 
FROM behaviour 
WHERE stop_distance = (SELECT MIN(stop_distance) 
                       FROM behaviour
                       WHERE mac = '10:A5:D0:06:C6:E9' 
                         AND stop_name = 'Stadthalle')

However, note that you don't really need a subquery here, you can simply order by lowest and select the first row.

SELECT mac, stop_name, stop_distance, speed
FROM behaviour
WHERE mac = '10:A5:D0:06:C6:E9'
  AND stop_name = 'Stadthalle'
ORDER BY stop_distance
LIMIT 1;

Upvotes: 0

The Reason
The Reason

Reputation: 7973

You dont need subquery which returns Min distance just add Order by in your query and you will get your result 

  SELECT mac, stop_name, stop_distance, speed 
    FROM behaviour 
    WHERE mac = '10:A5:D0:06:C6:E9' 
          AND stop_name = 'Stadthalle'
    ORDER By stop_distance 
    LIMIT 1

Upvotes: 1

Related Questions