http://www.brentozar.com/archive/2009/04/getting-the-most-recent-record/
How this works? What is ttNewer? What is a clustered primary key in mysql?
This is as good as I can do to get this into pg:
create table TestTable (id int not null,create_date date not null,info1 VARCHAR(50) NOT NULL,info2 VARCHAR(50) NOT
NULL,constraintPK_TestTable PRIMARY KEY(id,create_date)
);
INSERT INTO TestTable (id, create_date, info1, info2)
VALUES (1, '1/1/09', 'Blue', 'Green');
INSERT INTO TestTable (id, create_date, info1, info2)
VALUES (1, '1/2/09', 'Red', 'Yellow');
INSERT INTO TestTable (id, create_date, info1, info2)
VALUES (1, '1/3/09', 'Orange', 'Purple');
INSERT INTO TestTable (id, create_date, info1, info2)
VALUES (2, '1/1/09', 'Yellow', 'Blue');
INSERT INTO TestTable (id, create_date, info1, info2)
VALUES (2, '1/5/09', 'Blue', 'Orange');
INSERT INTO TestTable (id, create_date, info1, info2)
VALUES (3, '1/2/09', 'Green', 'Purple');
INSERT INTO TestTable (id, create_date, info1, info2)
VALUES (3, '1/8/09', 'Red', 'Blue');
select tt.* FROM TestTable tt LEFT OUTER JOIN TestTable ttNewer ON tt.id = ttNewer.id AND tt.create_date <
ttNewer.create_dateWHERE ttNewer.id IS NULL;
id | create_date | info1 | info2
----+-------------+--------+-------- 1 | 2009-01-03 | Orange | Purple 2 | 2009-01-05 | Blue | Orange 3 | 2009-01-08
| Red | Blue
(3 rows)