Last active
August 18, 2020 11:26
-
-
Save IlyaDonskikh/90b3111bcd29329b0ae87f0f8e1424da to your computer and use it in GitHub Desktop.
psql.samples
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT device.id, device.name, device.created_at FROM devices as device | |
WHERE ( | |
( | |
SELECT device_id FROM device_zones AS zones | |
WHERE (zones.id IS NOT NULL AND zones.device_id = device.id) | |
LIMIT 1 | |
) IS NOT NULL | |
) LIMIT 4; | |
--- | |
WITH uniq_devices AS ( | |
SELECT DISTINCT ON (device.id) device.id FROM devices as device JOIN device_zones as zones ON device.id = zones.device_id | |
) | |
SELECT id, name FROM devices WHERE id IN (SELECT id FROM uniq_devices); | |
--- | |
SELECT id, name FROM devices WHERE id IN (SELECT DISTINCT ON (device.id) device.id FROM devices as device JOIN device_zones as zones ON device.id = zones.device_id); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment