Re: SQL TRICK. Select rowid, max(...)

Jim Burks writes: > [shortened - BK] doctorq@sam (DoctorQ Komputery i Programy) writes: > >We've discussed problem with max() function with friends and we think that > >SQL syntax should be extended: > >There should be 'IS MAX' clause in WHERE section, similarly to 'IS NULL' > >clause. > > > >For example: > >select rowid from table where col is max [....]> >What do you think about it, Informix people? > > > > I think the current implementation is correct. > > MAX() is not a feature of a single row. > Multiple rows may match MAX(col). > ----------------------------------------------------------------- > Jim Burks jburks@promus.com > The Promus Companies > Memphis, Tennessee, USA At first glance, I thought it was a good idea. However, I thought it would be better for it to be worked into the ANSI standard for SQL, rather than another Informix extention to the standard -- just so those interested in database independence could still use the functionality fairly consistently. Jim brings up an interesting point, that "where col is max" could return multiple rows. This syntax does not allow you to guarantee that only one row would be returned -- a useful capability in some cases. Unfortunately, on the flip side, there isn't currently a simple way to get all rows with "max" without doing two selects, or a nested select (one to get the max value, one to search for rows that match it). Unless I missed something in that discussion recently.... -- Bryan Klopfenstein bryan@carsinfo.com CARS Information Systems Corp. Cincinnati, OH Standard Disclaimers Apply