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

id (primary), propertyId, status, updatedAt

All record - select * from property_alert_status

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:

Expected Output should be:

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:

The final query gives result:

Note: Using Mysql v5.6.50.

CodePudding user response：

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

CodePudding user response：

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