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

> 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. > 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(). > > Michal Hobot To some extent I would agree, in that it would make writing some queries easier. But you do not need to extend the syntax to achieve this kind of query. You can do it using a correlated sub-query. For example select * from city_log a wherechnge_ts in (select max(chnge_ts) from city_log b where a.city_nm = b.city_nm) This select retrieves the latest copy of changes to rows in a table from a buddy log table. Performance is really the issue and that is dependant on the optimiser used. Both methods of writing the query can use either a second select on the table for each city group or use a filter to filter out all but the highest value row for each city group from the main select. As the optimiser is improved I would expect these kind of filter v sub-query choices to be built in based on the collected stats. It is possible that as both methods must retreive and review all rows that there isn't much performance difference at the engine level. Now one of my current problems is to pick up the latest change but where the log table contains before and after images. Deletes show up as only before records, inserts as only afters, updates have both. Just out of curiosity does anyone see how to write a single SQL statement to give the latest change for each business key showing the deletes, inserts and both before and after update records. trnsction serial image_cd char(1) Before/After city_nm char(30) Business Key ... chnge_ts datetime In any case my SQL extension requests would be headed by asking the ANSI SQL committee which Informix appears to follow as closely as possible to adopt the Oracle syntax for walking down a hiererchy. This is the old parts explosion problem where you have a main table and a secondary table which shows which rows in the main table are subordinate to other rows in the main table. At the moment you need to write a recursive procedure to do this in Informix. Cheers - Jim My opinions are my own. They may vary with time but they remain MINE! ---------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 375-5222 (Work) Address: 700 Airport Blvd. #300 (415) 775-7762 (Home) Burlingame, CA 94010-1937 Fax: (415) 375-5019 ----------------------------------------------------------------------