1

I have a table _sync_history with this data:

+----+----------------+---------------------+
| id | object_name    | sync_time           |
+----+----------------+---------------------+
|  1 | territory      | 2015-07-21 22:29:02 |
|  2 | user           | 2015-07-21 22:29:19 |
|  3 | userterritory  | 2015-07-21 22:29:38 |
|  4 | account        | 2015-07-21 22:30:05 |
|  5 | accountshare   | 2015-07-21 22:39:04 |
|  6 | address_vod__c | 2015-07-22 00:47:38 |
|  7 | group          | 2015-07-22 01:23:30 |
|  8 | recordtype     | 2015-07-22 01:23:51 |
|  9 | user           | 2015-07-23 13:04:20 |
| 10 | account        | 2015-07-23 13:04:59 |
+----+----------------+---------------------+

I want to get the rows on user between 2015-07-21 22:29:19 and 2015-07-23 13:04:20 meaning the rows between last sync_time and previous sync_time for a given object_name. I was doing this:

SELECT * FROM `user` WHERE lastmodifieddate > (SELECT sync_time FROM `_sync_history` WHERE object_name='user' ORDER BY sync_time ASC LIMIT 1)

But that will give me just the ones from last sync_time and above which is not right, how do I get the ones I need?

4

You need to find both times, one using what you have (LIMIT 1) and the second using LIMIT 1 OFFSET 1.

I'd also use ORDER BY ... DESC, not ASC. Your wording suggests you want the rows between the last 2 appearnces of 'user':

SELECT * 
FROM user 
WHERE lastmodifieddate > 
        (SELECT sync_time 
         FROM _sync_history 
         WHERE object_name = 'user' 
         ORDER BY sync_time DESC 
         LIMIT 1 OFFSET 1
        )
  AND lastmodifieddate <
        (SELECT sync_time 
         FROM _sync_history 
         WHERE object_name = 'user' 
         ORDER BY sync_time DESC 
         LIMIT 1 OFFSET 0
        ) ;
2

You could do it using UDV:

#MIN sync_time of user
SET @StartDate=(SELECT MIN(sync_time) FROM test._sync_history WHERE object_name='user' LIMIT 0,1);

#MAX sync_time of user
SET @EndDate=(SELECT MAX(sync_time) FROM test._sync_history WHERE object_name='user' LIMIT 0,1);

#Query:
SELECT * FROM test.user WHERE lastmodifieddate BETWEEN @StartDate AND @EndDate;
-1

From what I gather I would do:

SELECT *
FROM user
WHERE lastmodifieddate IN ( SELECT min(sync_time), max(sync_time)
                            FROM _sync_history
                            WHERE object_name= "user" );
  • This is not valid syntax. You would have to use UNION ALL in the subquery and have SELECT lists with a single column (MIN and MAX respectively). – ypercubeᵀᴹ Jul 23 '15 at 14:58
  • You are right. My apology. Is this what you mean? SELECT * FROM user WHERE lastmodifieddate IN ( SELECT min(sync_time) FROM _sync_history WHERE object_name= "user" UNION ALL SELECT max(sync_time) FROM _sync_history WHERE object_name= "user"); – dia Jul 23 '15 at 15:06
  • in finds values in a set, not in between two values. For that you would need between <a> and <b> but that won't work with a subquery in this way. – Colin 't Hart Jul 23 '15 at 15:09
  • 1
    Yes, @Colin'tHart is right. In Postgres, you could use the "element is contained by" operator for ranges, with something like lastmodifieddate <@ (SELECT tsrange(min(sync_time), max(sync_time)) FROM _sync_history ...) but MySQL does not have these types and operators. – ypercubeᵀᴹ Jul 23 '15 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.