Get most recent records from the table if a 'ALERT' status is present otherwise most recent record with 'OK' Status - Mysql v5.6.50

Question

In Mysql I have the following table - property_alert_status having columns :

id (primary), propertyId, status, updatedAt

All record - select * from property_alert_status

id	propertyId	status	updatedAt
1	1	ALERT	1658304031
2	2	OK	1658300273
3	3	ALERT	1658312336
4	3	ALERT	1658313979
5	3	OK	1658312336
6	2	OK	1658312336

From the above table, I want to fetch the most recent record for the property based on status. If Status is 'ALERT' then most recent 'ALERT' record otherwise Most recent 'OK' record.

Ex - For propertyId '3' there are three records but most recent alert status is of id 4 so the output for the above propertyId 3 should be:

id	propertyId	status	updatedAt
4	3	ALERT	1658313979

Expected Output should be:

id	propertyId	status	updatedAt
1	1	ALERT	1658304031
4	3	ALERT	1658313979
6	2	OK	1658312336

I have made one query but the output is not as expected:

Select mainStatus.* from (
SELECT *
FROM property_alert_status
ORDER BY
(CASE
    WHEN status = "ALERT" THEN 0
    ELSE 1
END) ASC, updatedAt DESC
) mainStatus group by  propertyId;

Innerquery is giving the right result but when selecting only a single record by grouping propertyId, giving the wrong result.

Inner query giving result:

id	propertyId	status	updatedAt
4	3	ALERT	1658313979
3	3	ALERT	1658312336
1	1	ALERT	1658304031
5	3	OK	1658312336
6	2	OK	1658312336
2	2	OK	1658300273

The final query gives result:

id	propertyId	status	updatedAt
1	1	ALERT	1658304031
2	2	OK	1658300273
3	3	ALERT	1658312236

Note: Using Mysql v5.6.50.

Answer 1

Tables in SQL are unordered data set. A query result is a table. So the ORDER BY clause in your subquery doesn't have to sort the rows. Don't rely on it. Some DBMS even raise an error when you have an ORDER BY at the end of a subquery.

Moreover, select * from ... group by ... is invalid. If you group by a column, you can select that column plus aggregates, i.e. sums, maximums, averages and so on. You cannot select other original column values (except for the case they are functionally dependent on your group, such as a person's name when you group by the person's ID). MySQL should raise an error, and if it doesn't, this probably means that you are working in a cheat mode that MySQL invented in their early days. Make sure to always SET sql_mode = 'ONLY_FULL_GROUP_BY'; when working with MySQL in order to have the DBMS help you with invalid aggregation queries.

As to the task: You can rank your rows with ROW_NUMBER.

SELECT *
FROM
(
  SELECT
    s.*,
    ROW_NUMBER() OVER (PARTITION BY propertyid ORDER BY status, updatedat DESC) AS rn
  FROM vk_property_temperature_alert_status s
  WHERE temperature_status IN ('ALERT', 'OK')
) ranked
WHERE rn = 1;

For old MySQL versions I see two approaches. Either select only those rows for which not exists a better row or select those rows that are the best for their group. The second approach seems easier. It's basically writing a subquery that determines the top row for the property ID, so you can check whether the row you are looking at is a top row.

SELECT *
FROM vk_property_temperature_alert_status s
WHERE id =
(
  SELECT s2.id
  FROM vk_property_temperature_alert_status s2
  WHERE s2.temperature_status IN ('ALERT', 'OK')
  AND s2.propertyid = s.propertyid
  ORDER BY s2.status, s2.updatedat DESC
  LIMIT 1
);

Answer 2

I don't know if this will work, wrote just for fun as the question was very interesting:

SELECT 
MAX(maxId),
propertyId,
`status`,
MAX(dates) updatedAt
FROM
( 
SELECT 
firstResult.*,
(CASE WHEN @running_propertyId=0 THEN @running_propertyId:=propertyId ELSE @running_propertyId:=@running_propertyId END) runningPro,
(CASE WHEN @running_status='' THEN @running_status:=`status` ELSE @running_status:=@running_status END) runningStat,
(CASE WHEN  @running_variable >0 AND @running_propertyId =propertyId   THEN  @running_variable:=@running_variable+1 ELSE @running_variable:=1 END )var,

(CASE WHEN  @running_variable =1    THEN  @running_date:=updatedAt ELSE (CASE WHEN  `status`='ALERT' THEN @running_date:=updatedAt ELSE 
   ( CASE WHEN @running_status=`status` THEN @running_date:=updatedAt ELSE @running_date:=@running_date END) END) END )dates,

(CASE WHEN  @running_variable =1    THEN  @running_id:=id ELSE (CASE WHEN  `status`='ALERT' THEN @running_id:=id ELSE 
( CASE WHEN @running_status=`status` THEN @running_id:=id ELSE @running_id:=@running_id END) END) END )maxId,


@running_propertyId:=propertyId,
@running_status:=`status`
FROM (SELECT 
   a.*,
   @running_propertyId:=0,
   @running_status:='',
   @running_variable:=0,
   @running_date:=0,
   
   @running_id:=0
  FROM
    property_alert_status a 
 ORDER BY 
 `propertyId`
 ,`updatedAt`) firstResult
  ) final
  GROUP BY  propertyId

Answer 3

By combining my query with some changes into the @Thorsten Kettner's query -

Following Query giving expected result:

SELECT
   *
FROM
   property_alert_status s
WHERE
   id = (SELECT
           s2.id
       FROM
           property_alert_status s2
       WHERE
           s2.propertyId = s.propertyId
       ORDER BY (CASE
           WHEN s2.status = 'ALERT' THEN 0
           ELSE 1
       END) ASC, s2.updatedAt DESC
       LIMIT 1);