Last active
September 30, 2018 18:05
-
-
Save cowboy/21cd46eaa816e9d3a13509b15a43257e to your computer and use it in GitHub Desktop.
postgres sql query
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
-- This is the result I want: | |
-- | |
-- foo | bar | description | |
-- -----+-----+------------- | |
-- 3 | 4 | two | |
-- 1 | 2 | four | |
-- 5 | 6 | five | |
-- | |
-- Which I can get with this query, but can I do it | |
-- more simply? | |
WITH latest AS ( | |
SELECT | |
foo, | |
bar, | |
MAX(created_at) AS created_at | |
FROM my_table | |
GROUP BY foo, bar | |
) | |
SELECT | |
mt.foo, | |
mt.bar, | |
description | |
FROM latest | |
INNER JOIN my_table mt | |
ON mt.foo = latest.foo | |
AND mt.bar = latest.bar | |
AND mt.created_at = latest.created_at | |
ORDER BY id; |
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
CREATE TABLE my_table ( | |
id SERIAL PRIMARY KEY, | |
foo INTEGER, | |
bar INTEGER, | |
description TEXT, | |
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP | |
); | |
INSERT INTO my_table | |
(id, foo, bar, description, created_at) | |
VALUES | |
(1, 1, 2, 'one', '2016-06-28 14:40:00.574059+00'), | |
(2, 3, 4, 'two', '2016-06-28 14:40:04.416476+00'), | |
(3, 5, 6, 'three', '2016-06-28 14:40:07.733142+00'), | |
(4, 1, 2, 'four', '2016-06-28 14:40:11.281644+00'), | |
(5, 5, 6, 'five', '2016-06-28 14:40:14.616076+00'); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment