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 Databases