From: Ray Stell <stellr@cns.vt.edu>
To: pgsql-sql@postgresql.org
Subject: mysql code questions
Date: Wed, 12 Aug 2009 14:19:28 -0400
Message-ID: <20090812181928.GF7527@cns.vt.edu> (raw)
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: stellr@cns.vt.edu
Subject: Re: mysql code questions
In-Reply-To: <20090812181928.GF7527@cns.vt.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox