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

Bryan Klopfenstein (bryan@carsinfo.com) writes: -> ->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 [1] ->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 [2] ->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.... [3] ->-- ->Bryan Klopfenstein bryan@carsinfo.com ->CARS Information Systems Corp. ->Cincinnati, OH Standard Disclaimers Apply [1] On first seeing Michal Hobot (doctorq@sam)'s suggestion, I liked it, too. I said as much in my comments, posted earlier. But I also came up with and discussed some syntactic and semantic problems from trying to extend SQL this way. Your suggestion to try to amend the ANSI standard, rather than provide an extension for this case, is very good. [2] I kind of assumed this. All WHERE clauses have the potential to return more than one row. The only SELECT that I can think of that is quaranteed to return at most one row is one containing only aggregates (MAX, MIN, etc.) with no GROUP BY clauses. [3] I find the timing of messages in this forum kind of strange sometimes. It's not uncommon for me to receive the replies to messages before the messages themselves. I haven't figured this out yet. In summary, I think Michal Hobot has a good basic idea, but it could use some refinement. Thanks, Michal, for bringing it up! Now if someone with some clout on the ANSI SQL committee is interested in taking up this subject, we might get some results. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\