ɐuıɥɔɐɯ ɐuıɥɔɐɯ - 3 years ago 82
SQL Question

Select IDs (same table) that don't have any records/rows after a specific date

I have a table, let's say, like this one:

|--------------------------------------+--------------------------------------+--------+----------------+---------------------|
| user_id | mortgage_id | value | classification | created_at |
|--------------------------------------+--------------------------------------+--------+----------------+---------------------|
| 6c1e1f12-2e5d-488d-b02d-29fcffe783f2 | 1e76bcbb-70ee-4966-87fd-1d6024a04513 | 0 | initial | 2014-08-23 14:25:42 |
|--------------------------------------+--------------------------------------+--------+----------------+---------------------|
| 49dc3dab-d2d0-400b-b964-71e03339d475 | 59366911-f1a8-4a8c-b7ea-c3257d04478e | 1 | created | 2015-08-23 14:26:11 |
|--------------------------------------+--------------------------------------+--------+----------------+---------------------|
| 76ce889b-2f2c-435f-8754-7c5ec15cbfcb | b962e26b-1ba6-4547-8eb8-167989a0705e | 5 | created | 2016-08-23 14:26:11 |
|--------------------------------------+--------------------------------------+--------+----------------+---------------------|
| 5d9f1892-05c0-4b0a-b5d9-a501595fa351 | fb4be36e-e156-4c1b-bd40-422d30646f8e | 8 | created | 2016-08-23 14:26:11 |
|--------------------------------------+--------------------------------------+--------+----------------+---------------------|
| 49dc3dab-d2d0-400b-b964-71e03339d475 | 2cee0bc7-744f-4f51-a094-f5eb66ac482e | 2 | created | 2017-08-23 14:26:11 |
|--------------------------------------+--------------------------------------+--------+----------------+---------------------|
| 76ce889b-2f2c-435f-8754-7c5ec15cbfcb | b0d27c9e-907c-43df-abd2-5772785cb91c | 0 | created | 2017-08-23 14:26:11 |
|--------------------------------------+--------------------------------------+--------+----------------+---------------------|



I'm trying to fetch/get all the distinct/unique
user_id
s that don't have any records from a given moment in time and onwards.


For instance, if I choose that "time frame" to be:
After 2017-01-01 00:00:00
, the return would be:

|--------------------------------------+
| user_id |
|--------------------------------------+
| 6c1e1f12-2e5d-488d-b02d-29fcffe783f2 |
|--------------------------------------+
| 5d9f1892-05c0-4b0a-b5d9-a501595fa351 |
|--------------------------------------+


I have this query, but I think there should be a better way to do this:

SET @timestamp = '2017-01-01 00:00:00';
SELECT DISTINCT user_id
FROM mortgages
WHERE user_id NOT IN (SELECT DISTINCT user_id FROM mortgages WHERE created_at > @timestamp);

Answer Source

I would use group by:

select user_id
from mortgages
group by user_id
having max(created_at) <= @timestamp;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download