Select v MySql
Karel Zak
zakkr na zf.jcu.cz
Středa Listopad 21 10:44:36 CET 2001
On Wed, Nov 21, 2001 at 10:21:51AM +0100, Radek Kanovsky wrote:
> On Wed, Nov 21, 2001 at 09:59:15AM +0100, Honza Pazdziora wrote:
> S GROUP BY to nejde a ten prvni priklad nefunguje kvuli vic
> polozkam v subselectu. Zkuste to nejak takto:
>
> SELECT c_prod.id_prod, c_prod.nazov, c_ver.verzia, c_ver.datum
> FROM c_prod, c_ver
> WHERE c_prod.id_prod = c_ver.id_prod
> AND c_ver.verzia IN (SELECT MAX(verzia) FROM c_ver GROUP BY id_prod);
Taky spatne :-)
Protoze pokud bude nejake nejvyssi verzi produktu 'ccc' odpovidat
nejaka nizsi verze produktu 'aaa' tak 'aaa' budete mit ve vysledku
vicekrat protoze bude platit to IN. Priklad:
DATA:
test=# select n.name, v.ver, v.dat from ver v, names n where
n.id=v.id order by name, ver;
name | ver | dat
------+------+------------------------
aaa | 2 | 2001-12-31 00:00:00+01
aaa | 2.75 | 2001-06-15 00:00:00+02
aaa | 3.1 | 2001-08-01 00:00:00+02
bbb | 5.1 | 2001-01-01 00:00:00+01
bbb | 5.2 | 2001-02-01 00:00:00+01
ccc | 1.89 | 2001-11-03 00:00:00+01
ccc | 1.98 | 2001-11-04 00:00:00+01
ccc | 2 | 2001-12-04 00:00:00+01
(8 rows)
DOTAZ:
test=# select n.name, v.ver, v.dat from ver v, names n where
n.id=v.id and v.ver in (select max(ver) from ver group by id);
name | ver | dat
------+-----+------------------------
aaa | 3.1 | 2001-08-01 00:00:00+02
aaa | 2 | 2001-12-31 00:00:00+01
bbb | 5.2 | 2001-02-01 00:00:00+01
ccc | 2 | 2001-12-04 00:00:00+01
(4 rows)
Coz?
Karel
--
Karel Zak <zakkr na zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
Další informace o konferenci Databases