SQL - Select value with newest date-CodePudding

I am trying to select added value "state" with newest date from my table, but only if left join table object is visible. And only once per property and unit in. What i have done so far:

SELECT propertyUnitEnergyStates.id,
       propertyUnitEnergyStates.property_id,
       propertyUnitEnergyStates.unit_id,
       propertyUnitEnergyStates.type,
       propertyUnitEnergyStates.state,
       propertyUnitEnergyStates.date,
       propertyUnits.visible
  FROM propertyUnitEnergyStates
  LEFT JOIN propertyUnits
    ON propertyUnits.property_id = $propertyID
 WHERE propertyUnitEnergyStates.property_id = $propertyID
   AND propertyUnitEnergyStates.type = '$name'
   AND propertyUnits.visible = 1
 GROUP BY propertyUnitEnergyStates.unit_id
 ORDER BY propertyUnitEnergyStates.date DESC

What I am getting now is result where every propertyUnits.visible is 1. Even if in table is set to zero.

Object from propertyUnits. As you can see the value visible is 0 but i am getting 1.

Right now i noticed that, the "state" value which i want to be the newest is not the newest.

As you can see i have in my result for unit_id 5 value 853, but in the table propertyUnitEnergyStates is the newest value 400.

CodePudding user response：

Okay. Let's try like this.

WITH ranked_state AS (
  SELECT us.*, ROW_NUMBER() OVER (PARTITION BY unit_id ORDER BY date DESC) AS rn
  FROM propertyUnitEnergyStates us
  WHERE us.type = '$name' AND us.property_id = $propertyID
)
SELECT ranked_state.id, 
       ranked_state.property_id, 
       ranked_state.unit_id, 
       ranked_state.type, 
       ranked_state.state, 
       ranked_state.date, 
       pu.visible 
FROM ranked_state 
LEFT JOIN propertyUnits pu
ON ranked_state.property_id = pu.property_id AND pu.visible = 1
WHERE rn = 1

If I explain this query.. First, regarding the rows that meet the given condition(ex: us.type="$name", us.property_id=$propertyID), the rows with the same unit_id are ordered by date. After then do LEFT JOIN.

CodePudding user response：

I try different options, but I still can't get the result. It still ignores the visible attribute and does not return the last record by date.

Here is my last try:

$query = "SELECT UniEne.id, 
        UniEne.property_id, 
        UniEne.unit_id, 
        UniEne.type, 
        UniEne.state, 
        UniEne.date, 
        propUni.visible 
        FROM 
        (SELECT * FROM propertyUnitEnergyStates WHERE type = '$name' AND property_id = $propertyID ORDER BY date DESC) AS UniEne 
        INNER JOIN (SELECT * FROM propertyUnits WHERE property_id= $propertyID AND visible = 1) as propUni
        WHERE UniEne.property_id = $propertyID GROUP BY UniEne.unit_id";

Result is:

But value "state" is not correct. The last inserted have in unit_id(1) value 7000..