CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Snowy Icicles
Snowy Icicles

Reputation: 1

Filter out so only latest dates show to each unique ID

There's multiple equipment.ID's with the same ID, but different Condition_Dates, i would like to only show the newest condition dates of each unique Equipment.ID. 

Select equipment.ID as EquipmentID,
       equipment.DESCRIPTION, 
       CONDITION_DATE, 
       CONDITION_TYPE.DESCRIPTION
from CONDITION
  LEFT JOIN EQUIPMENT ON condition.GUID_EQUIPMENT = EQUIPMENT.GUID
  LEFT JOIN Condition_type ON Condition.GUID_CONDITION_TYPE = condition_type.guid
WHERE IS_DEACTIVATED = '0' 
  AND equipment.HAS_CONDITION_CONTROL = '1' 
  AND Condition_type.DESCRIPTION is not null

Equipment ID | Condition_Date
3345.02      | 2013-08-29
3345.02      | 2009-05-20 
3346         | 2019-07-05
3345.02      | 2001-01-01
3346         | 2008-08-02

I want it to only show 

Equipment ID | Condition_Date
3345.02      | 2013-08-29
3346         | 2019-07-05

Upvotes: 0

Views: 149

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want all equipment, then that should be the first table in the chain of LEFT JOINs.

However, you are checking for a match on condition type, so the LEFT JOINs are not even appropriate.

With that out of the way, I would recommend row_number() in a subquery on the conditions table:

select e.ID as EquipmentID, e.DESCRIPTION, 
       c.CONDITION_DATE, 
       ct.DESCRIPTION
from equipement e join
     (select c.*,
             row_number() over (partition by guid_equipment order by c.condition_date desc) as seqnum
      from condition c
     ) c
     on c.GUID_EQUIPMENT = e.GUID and
        seqnum = 1 join
     condition_type ct
     on c.GUID_CONDITION_TYPE = ct.guid
where e.IS_DEACTIVATED = 0 and         -- looks like a number, so no single quotes
      e.HAS_CONDITION_CONTROL = 1 and  -- looks like a number, so no single quotes
      ct.DESCRIPTION is not null

Upvotes: 0

Magisch
Magisch

Reputation: 7352

ROW_NUMBER() is your friend for this application on SQL server.

SELECT * FROM (
Select equipment.ID as EquipmentID,
       equipment.DESCRIPTION, 
       CONDITION_DATE, 
       CONDITION_TYPE.DESCRIPTION,
       ROW_NUMBER() OVER (Partition by equipment.ID ORDER BY CONDITION_DATE DESC) AS rownum
from CONDITION
  LEFT JOIN EQUIPMENT ON condition.GUID_EQUIPMENT = EQUIPMENT.GUID
  LEFT JOIN Condition_type ON Condition.GUID_CONDITION_TYPE = condition_type.guid
WHERE IS_DEACTIVATED = '0' 
  AND equipment.HAS_CONDITION_CONTROL = '1' 
  AND Condition_type.DESCRIPTION is not null ) WHERE rownum = 1

In your minimalistic example, this is equivalent to using a MAX Type solution while grouping as well, but if you want to conditionally pull other information from the records associated with the higher CONDITION_DATE, then you need ROW_NUMBER().

The way this works:

  • The ROW_NUMBER() assigns a rownumber (partitioned per equipment.ID) to each row returned, ordered by CONDITION_DATE. So the Newest record set will always have the ROW_NUMBER() 1. You abuse this by selecting for it in the WHERE part.

Upvotes: 0

Xabier
Xabier

Reputation: 7735

How about the following, aggregate using MAX on the date field and GROUP BY Id:

SELECT equipment.ID AS EquipmentID,
       MAX(CONDITION_DATE), 
FROM CONDITION
  LEFT JOIN EQUIPMENT 
         ON condition.GUID_EQUIPMENT = EQUIPMENT.GUID
  LEFT JOIN Condition_type 
         ON Condition.GUID_CONDITION_TYPE = condition_type.guid
WHERE IS_DEACTIVATED = '0' 
  AND equipment.HAS_CONDITION_CONTROL = '1' 
  AND Condition_type.DESCRIPTION IS NOT NULL
GROUP BY equipment.ID

Upvotes: 1

Related Questions