CODEWITHSUNDEEP
mysqlsqlmin
user1187968
user1187968

Reputation: 8016

Write SQL query to find rows that are near min() value

I have about 5000 rows of data as follow: 

id    date        temperature     room 
--------------------------------------
0    2013-07-15   76              A
1    2013-08-15   72              A
2    2013-09-15   74              B
3    2013-02-15   71              B
4    2013-03-15   72              B
5    2013-04-15   70              A
...
...
...
5000 2013-08-01   68              A

I can use the query from below to find min temperature in each room.

select room, min(temperature) from table_record group by room.

Now, I need to find all rows that are close to the the min temperature for each room. I have try using "join" on the same table as below, but it cannot be run. 

select t1.room, min(t1.temperature) from table_record t1 
  join on table_record t2 on 
            t2.room = t1.room and 
            t2.temperature * 0.95 (less_or_equal) min(t1.temperature)
  group by room

Upvotes: 2

Views: 403

Answers (1)

MatBailie
MatBailie

Reputation: 86765

You need to do this in two steps.

SELECT
  *
FROM
(
  SELECT room, MIN(temperature) AS min_temp FROM TABLE_RECORD GROUP BY room
)
  AS ROOM_TEMP
INNER JOIN
  TABLE_RECORD
    ON  TABLE_RECORD.room         = ROOM_TEMP.room
    AND TABLE_RECORD.temperature <= ROOM_TEMP.min_temp / 0.95

Provided that you have an index on (room, temperature) this should be pretty quick.

Also, note that I use x <= y / 0.95 rather than x * 0.95 <= y. This is to make the lookup faster (manipulate the search criteria once, rather than the searched field on every row).

Upvotes: 2

Related Questions