Date time Record ID Action
9/9/2018 7:03 102 Support
9/9/2018 7:03 102 hello
9/10/2018 7:03 103 Fetch it
9/10/2018 7:03 103 Ask it
9/10/2018 7:03 103 enable it
9/9/2018 5:03 104 support
9/9/2018 4:03 104 Fetch it
9/11/2018 7:03 105 Support
9/11/2018 8:03 105 Support
9/12/2018 5:03 106 end it
9/12/2018 6:03 106 Fetch it
9/12/2018 7:03 106 Support
What I am trying to achieve is
Count of Record ID where the last record (Date time arranged in ascending order) in the action column should have support only once and there should be no record after Support for every ID
In the above table only record ID's 106 and 104 have Support only once in action column and there is no record after support ordered ascending by date time So I need to count(2)/display 104 and 106 ..
Can someone help with this!!..
Hmmm. One method uses aggregation and having:
select recordid
from t
group by recordid
having min(case when action = 'Support' then datetime end) = max(datetime);
This essentially says that the first time "Support" is seen is the latest time for the recordid.