Select multi rows with MAX() condition

[beyzad]

Hi there.

 

I have 2 tables like this:

factor_times:
+------------+---------------------+--------+
| factor_key |     factor_time     | status |
+------------+---------------------+--------+
|     1      |     1313131313      |    1   |
|     1      |     1313131314      |    2   |
|     1      |     1313131315      |    1   |
|     1      |     1313131316      |    2   |
|     2      |     1313131317      |    1   |
|     2      |     1313131318      |    3   |
|     3      |     1313131319      |    1   |
|     3      |     1313131320      |    6   |
+------------+---------------------+--------+

and

factors:
+------------+---------------+
| factor_key | factor_status |
+------------+---------------+
|     1      |       2       |
|     2      |       3       |
|     3      |       6       |
+------------+---------------+

now, I want to make a query that will results all factors, Maximum time of current status and its status with specified time range.

 

I used a query like this:

SELECT `factors`.*,`factor_times`.`factor_time` AS `update` FROM `factors` LEFT JOIN `factor_times` ON `factors`.`factor_key`=`factor_times`.`factor_key` AND `factor_times`.`factor_status`=2 AND `factor_times`.`factor_time` <= 1313131320 AND `factor_times`.`factor_time` >= 1313131313 WHERE `factors`.`factor_status`='2'

I also must say that a factor can have multiple times in a single status. For example, factor status can be changed to 2, then 1, then again 2, then 3, then again 2 and go on.

 

Thanks

[Muddy_Funster]

Untested, but something like the following should be close:

 

SELECT
  factor_key,
  factor_m_time,
  factor_status
FROM
  factors
LEFT JOIN
  (SELECT factor_key, MAX(factor_time) AS factor_m_time, factor_status FROM factor_times GROUP BY factor_key) AS max_time_factors
ON
  factors.factor_key = max_time_factors.factor_key