Date: Wed, 12 Aug 2009 14:19:28 -0400
From: Ray Stell <stellr@cns.vt.edu>
To: pgsql-sql@postgresql.org
Subject: mysql code questions
Message-ID: <20090812181928.GF7527@cns.vt.edu>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.17 (2007-11-01)

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)