pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmysql code questions
3+ messages / 3 participants
[nested] [flat]
* mysql code questions
@ 2009-08-12 18:19 Ray Stell <stellr@cns.vt.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Ray Stell @ 2009-08-12 18:19 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: mysql code questions
@ 2009-08-12 18:52 Thomas Kellerer <spam_eater@gmx.net>
parent: Ray Stell <stellr@cns.vt.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Thomas Kellerer @ 2009-08-12 18:52 UTC (permalink / raw)
To: pgsql-sql
Ray Stell wrote on 12.08.2009 20:19:
> 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?
That article talks about SQL Server not MySQL.
> 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;
I would probably do it this way:
SELECT tt. *
FROM testtable tt
WHERE create_date = (SELECT MAX(create_date)
FROM testtable tt2
WHERE tt.id = tt2.id);
Don't know which one is more efficient (with just a few rows, it doesn't really pay off to look at
the execution plan)
Thomas
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: mysql code questions
@ 2009-08-12 19:00 Greg Stark <gsstark@mit.edu>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 0 replies; 3+ messages in thread
From: Greg Stark @ 2009-08-12 19:00 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
On Wed, Aug 12, 2009 at 7:52 PM, Thomas Kellerer<spam_eater@gmx.net> wrote:
> Ray Stell wrote on 12.08.2009 20:19:
> I would probably do it this way:
>
> SELECT tt. *
> FROM testtable tt
> WHERE create_date = (SELECT MAX(create_date)
> FROM testtable tt2
> WHERE tt.id = tt2.id);
The article is *very* unclear about the problem. You have to read the
whole schema and their proposed solution to figure out what the
problem they're trying to solve is. This query doesn't solve it.
They want the newest record for *each* id. Ie, the equivalent of
"select id,max(create_date) from tt group by id" except including the
other attributes for the record with that date.
There are a number of ways to solve it. Postgres supports all of the
solutions in the comments including the one proposed in the post as
well as the window-function based solution.
It also supports an extension which could be even more efficient than
all of them:
select DISTINCT ON (id) from tt order by id,create_date desc
If you have an index on <id,create_date desc> (Or if you have an index
on <id,create_date> and use "order by id desc, create_date desc" in
the query) then this could do a single index scan.
--
greg
http://mit.edu/~gsstark/resume.pdf
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-08-12 19:00 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-08-12 18:19 mysql code questions Ray Stell <stellr@cns.vt.edu>
2009-08-12 18:52 ` Thomas Kellerer <spam_eater@gmx.net>
2009-08-12 19:00 ` Greg Stark <gsstark@mit.edu>
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