Thread: mysql code questions

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)

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

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