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

->From: doctorq@sam (DoctorQ Komputery i Programy) ->Subject: Re: SQL TRICK. Select rowid, max(...) ->Date: 14 Mar 1994 14:38:49 GMT ->Reply-To: doctorq@sam (DoctorQ Komputery i Programy) ->Organization: Science And Accademic Computer Networks -> ->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 -> ->Why? ->Today, MAX() is treated like SUM() or COUNT(). But there is a big difference ->between them: SUM() and COUNT() are features of whole column of table. ->MAX() is feature of column, but it's also a feature of a row! ->You can pick rows with max (or min of course) value. Of course it's ->nonsense with SUM() or COUNT(). ->That's the difference. -> ->What do you think about it, Informix people? -> ->Michal Hobot ->doctorq@sam.nask.com.pl An interesting concept which seems useful. Let's see, I think the current equivalent to your query would be something like: select rowid from table where col = ( select max(col) from table );It's not too bad with only one such condition, even with current syntax. But what happens with: select rowid from table where col1 is max and col2 is max;Does this mean the row(s) having max(col2) among those that have max(col1)? If you don't assume the order implies major/minor key, then it is less likely that any row will have the max value for more than one column. However, assuming that the order implies major/minor key violates the current commutative nature of the "and" operator. One possible way out of this syntactic mess is "where col1, col2 is max". You might want to extend the concept to include "is median" and "is ave". The median row would always exist, except for empty tables, thought it might be SLOOOOW to determine for large tables. Max, min, and median work for all (non-blob?) data types. Average is a little harder. First it only applies to numeric, and maybe date(time), data types. The precise average is not always likely to be a data value, depending on the type of data. What about "is within xxx [percent] of ave" as a WHERE clause operator? Maybe that's going too far? 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!) \\ /________________________) (____________________________\\