Select v MySql
Karel Zak
zakkr na zf.jcu.cz
Středa Listopad 21 10:30:18 CET 2001
On Wed, Nov 21, 2001 at 09:59:15AM +0100, Honza Pazdziora wrote:
> On Wed, Nov 21, 2001 at 09:44:36AM +0100, Karel Zak wrote:
> > On Wed, Nov 21, 2001 at 08:57:13AM +0100, Jirka K. wrote:
> > > SELECT c_prod.id_prod, c_prod.nazov, c_ver.verzia FROM c_prod, c_ver WHERE
> > > c_ver .verzia=(SELECT c_ver.verzia, c_ver.datum FROM c_ver, c_prod WHERE
> > > c_ver.id_prod=c_prod.id_prod ORDER BY verzia DESC LIMIT 1,1)
> > > AND c_ver.id_prod=c_prod.id_prod
> > >
> >
> > Je to typicka ukazka pouziti GROUP BY:
> >
> > SELECT p.nazov, v.datum, max(v.verzia)
> > FROM c_ver v, c_prod p
> > WHERE v.id_prod=p.id_prod
> > GROUP BY p.nazov, v.datum;
>
> ale puvodni pozadavek smeruje na to, ze se ma pro dany produkt najit
> nejvyssi verze (1.74) a k ni jeji datum (13.12.2001).
>
> Obavam se, ze tohle cistym group by neudelate.
Oops... omlouvam se Jirkovi K., zkousel jsem si to bez toho datumu,
respektive s prazdnym slupcem datumu. Pokud by to chtel tazatel bez
datumu tak by GROUP BY slo. Pochopitelne z datumem se to nagroupuje
blbe... ALE abych se tak snadno nevzdal :-) ... tak pokud by platilo,
ze vyssi verze daneho prouduktu ma vzdy vyssi datum tak je mozne
pouzit max() na datum v dane groupe:
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.75 | 2001-06-15 00:00:00+02
aaa | 2.85 | 2001-07-01 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)
test=# select n.name, max(v.ver), max(v.dat) from ver v,
names n where n.id=v.id group by n.name;
name | max | max
------+-----+------------------------
aaa | 3.1 | 2001-08-01 00:00:00+02
bbb | 5.2 | 2001-02-01 00:00:00+01
ccc | 2 | 2001-12-04 00:00:00+01
(3 rows)
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 Test