Skip to content

Instantly share code, notes, and snippets.

@cowboy
Last active September 30, 2018 18:05
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save cowboy/21cd46eaa816e9d3a13509b15a43257e to your computer and use it in GitHub Desktop.
Save cowboy/21cd46eaa816e9d3a13509b15a43257e to your computer and use it in GitHub Desktop.
postgres sql query
-- 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;
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');
@gnarf
Copy link

gnarf commented Jun 28, 2016

WITH ranked as (
    SELECT *, RANK() OVER (PARTITION BY foo, bar ORDER BY created_at DESC) rank FROM my_table
)
SELECT 
    foo, bar, description
FROM ranked
WHERE rank=1
ORDER BY id;

@cowboy
Copy link
Author

cowboy commented Jun 28, 2016

Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment