Skip to content

Instantly share code, notes, and snippets.

@sleroux
Created September 13, 2016 14:50
Show Gist options
  • Save sleroux/07f060b594903eb23bdfc3df642493a9 to your computer and use it in GitHub Desktop.
Save sleroux/07f060b594903eb23bdfc3df642493a9 to your computer and use it in GitHub Desktop.
SELECT *
FROM (
SELECT * FROM (
SELECT history.id AS historyID, history.url AS url, history.title AS title, NULL AS latest, (SELECT count(1) FROM visits WHERE visits.siteID = history.id) as visitCount
FROM (
SELECT bmkUri
FROM view_bookmarksLocal_on_mirror
WHERE view_bookmarksLocal_on_mirror.server_modified > 1472836869199 OR view_bookmarksLocal_on_mirror.local_modified > 1472836869199
)
LEFT JOIN history ON history.url = bmkUri
WHERE visitCount >= 3 AND history.title NOT NULL and history.title != ''
LIMIT 1
)
UNION
SELECT * FROM (
SELECT history.id as historyID, url, title, latest, (SELECT COUNT(1) FROM visits WHERE s = visits.siteID) AS visitCount
FROM (
SELECT siteID AS s, max(date) AS latest
FROM visits
WHERE date > 1472836866607250
GROUP BY siteID
)
LEFT JOIN history ON history.id = s
WHERE visitCount <= 3 AND title NOT NULL AND title != ''
LIMIT 19
)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment