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