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,
constraint PK_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_date
WHERE 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)
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
