1
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!!..

user3369545
  • 310
  • 2
  • 14
  • Record 102 is rather ambiguous. Your data doesn't have enough information to determine which is last. Also, you should tag the question with the database you are using. – Gordon Linoff Sep 24 '18 at 21:33
  • Sure Gordon!..I will take your feedback...Will tag the question with the database that I am using going forward....Apologies for the ambiguity!.. – user3369545 Oct 18 '18 at 18:04

1 Answers1

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786