Hello Jesper,
Jesper Palmqvist [Talarforum i Skandinavien AB] wrote:
> I have a question regarding ORDER BY in JOINs
>
> I have 3 tables, and a query (simplifed below) where I want to return the latest
> version of tbl2.info, where tbl2.timeofchange is a DATETIME parameter specifying when
> tbl2.info was last edited. The query below does execute, but does not return what I want.
> Also I would like to have the highest value of id3 returned, that doesn't work either.
> What is wrong with the query below.
>
> SELECT tbl1.id1, tbl2.id2, tbl2.timeofchange2, tbl2.info2, tbl3.id3
> FROM ((tbl1 INNER JOIN tbl2 ON tbl1.id1 = tbl2.id1) INNER JOIN tbl3 ON tbl2.id1 =
> tbl3.id1)
> GROUP BY tbl1.id1 ORDER BY tbl1.id1, tbl3.id3 DESC , tbl2.id2 DESC,
> tbl2.timeofchange2 DESC LIMIT 0 , 2
>
> Returns
> id1;id2;timeofchange2;info2;id3
> 1;1;2008-01-01 00:00:00;Foo1;1
> 2;5;2008-05-01 00:00:00;Foo5;4
>
> Expected (but not returned) result:
> 1;4;2008-04-01 00:00:00;Foo4;3
> 2;5;2008-05-01 00:00:00;Foo5;4
>
>
> CREATE TABLE IF NOT EXISTS `tbl1` (
> `id1` int(11) NOT NULL,
> `name1` varchar(20) NOT NULL,
> PRIMARY KEY (`id1`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
>
> INSERT INTO `tbl1` (`id1`, `name1`) VALUES
> (1, 'Wolf'),
> (2, 'Dog');
>
> CREATE TABLE IF NOT EXISTS `tbl2` (
> `id1` int(11) NOT NULL,
> `id2` int(11) NOT NULL,
> `timeofchange2` datetime NOT NULL,
> `info2` varchar(255) NOT NULL,
> PRIMARY KEY (`id2`),
> KEY `id1` (`id1`),
> KEY `timeofchange2` (`timeofchange2`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
>
> INSERT INTO `tbl2` (`id1`, `id2`, `timeofchange2`, `info2`) VALUES
> (1, 1, '2008-01-01 00:00:00', 'Foo1'),
> (1, 2, '2008-02-01 00:00:00', 'Foo2'),
> (1, 3, '2008-03-01 00:00:00', 'Foo3'),
> (1, 4, '2008-04-01 00:00:00', 'Foo4'),
> (2, 5, '2008-05-01 00:00:00', 'Foo5');
>
>
> CREATE TABLE IF NOT EXISTS `tbl3` (
> `id1` int(11) NOT NULL,
> `id3` int(11) NOT NULL,
> `data3` varchar(100) NOT NULL,
> PRIMARY KEY (`id3`),
> KEY `id1` (`id1`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> INSERT INTO `tbl3` (`id1`, `id3`, `data3`) VALUES
> (1, 1, 'Data1'),
> (1, 2, 'Data2'),
> (1, 3, 'Data3'),
> (2, 4, 'Data4');
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=1
>
This is another example of a "groupwize maximum" query pattern. There
are dozens of ways to solve this. We even list a few in the manual:
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
I prefer to break the problems into stages and use temporary tables for
each step. But there are ways to write this query as a single statement.
Warning: the single-statement versions are usually much slower.
# stage 1: find the latest timeofchange2 for each id1 value on tbl2
#
CREATE TEMPORARY TABLE tmp1 ENGINE=MEMORY
SELECT id1, max(timeofchange2) as latest
FROM tbl2
GROUP BY id1;
ALTER TABLE tmp1 ADD KEY(id1,latest);
#
# stage 2: find the highest 1d3 value for each 1d1 value on tbl3
#
CREATE TEMPORARY TABLE tmp2 ENGINE=MEMORY
SELECT id1, max(id3) as max3
FROM tbl3
GROUP BY id1;
#
# stage 3: build the final report based on the values we collected above
#
SELECT tmp1.id1
, tbl2.id2
, tmp1.timeofchange2
, tbl2.info2
, tmp2.id3
FROM tmp1
INNER JOIN tmp2
ON tmp1.id1 = tmp2.id1
INNER JOIN tbl2
ON tmp1.id1 = tbl2.id1
AND tmp1.latest = tbl2.timeofchange2;
#
# always cleanup after yourself
#
DROP TEMPORARY TABLE tmp1, tmp2;
Let us know if you have problems converting this to work with your real
data.
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
