Search code examples
sqloracletimetimestamporacle-sqldeveloper

How to select only these IDs which shows in table for the first time during last 30 days in Oracle SQL?

I have table in Oracle SQL presents ID of clients and date with time of their login to application:

ID | LOGGED
----------------
11 | 2021-09-10 12:55:13.278
11 | 2021-08-10 13:58:13.211
11 | 2021-02-11 12:22:13.364
22 | 2021-09-15 08:34:13.211
33 | 2021-04-02 14:21:13.272

How can I select only these IDs, which logged the first time during last 30 days ? So as a result I need something like below:

ID
---
22

Because only ID 22 logged first time during last 30 days -> 2021-09-15 08:34:13.211 How can I do that in Oracle SQL ?

Solution

  • Use this

        Select id from table where trunc(logged) 
    >= 
    Trunc(sysdate-30) group by id having count(*) =1

    Or better condition is using min, max

         Select id from table where trunc(logged) 
    >= 
    Trunc(sysdate-30) group by id having min(logged) 
     =max(logged)