db.sql

-- Nuvel, dette script kan udføres med PostgreSQL 9.1 på en vilkårlig database.
-- Det burde ikke lave nogen ændringer, thi alt foregår i en transaktion:
BEGIN;

-- Byg passende legetable...
CREATE TEMPORARY TABLE posts (
  uid INTEGER,
  nid INTEGER,
  title TEXT,
  created DATE );

-- Med passende Index
CREATE INDEX posts_frontpage_idx ON posts (uid, created);

-- Fyld op med legedata
INSERT INTO posts VALUES (1, 1, 'Skidtfisk', '2001-01-01');
INSERT INTO posts VALUES (1, 2, 'Kanel', '2006-01-01');
INSERT INTO posts VALUES (1, 3, 'Kardemomme', '2003-01-01');

INSERT INTO posts VALUES (2, 1, 'Drupal', '2002-01-01');
INSERT INTO posts VALUES (2, 7, 'MediaWiki', '2000-01-01');
INSERT INTO posts VALUES (2, 5, 'Node.JS', '1997-01-01');

INSERT INTO posts VALUES (3, 2, 'RB-trees', '1997-01-01');
INSERT INTO posts VALUES (3, 3, 'KD-trees', '1999-01-01');
INSERT INTO posts VALUES (3, 4, 'Treaps', '2009-01-01');

-- Query, DISTINCT ON...
SELECT DISTINCT ON (uid) uid, nid, title
FROM posts
ORDER BY uid, created DESC;

-- Via Views
CREATE VIEW last_post AS
  SELECT uid, max(created) as created
  FROM posts
  GROUP BY uid;

SELECT uid, nid, title
FROM posts INNER JOIN last_post USING (uid, created);

-- Via SubQueries
SELECT uid, nid, title
FROM posts INNER JOIN (SELECT uid, max(created) AS created
                       FROM posts
                       GROUP BY uid) AS sq USING (uid, created);

ROLLBACK;