Re: Help with a SELECT statement to return MAX record!

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Sat, 03 Apr 1999 23:25:53 GMT
Message-ID: <37159704.4888241@netnews.worldnet.att.net>

On Fri, 02 Apr 1999 09:06:33 GMT, adrianh_at_globalnet.co.uk (Adrian Harrison) wrote:

>select max(substr(farm_code,5,4))max_num from nvz_farms where substr(farm_code,1,4) = 'BRID';

This is the only thing that comes to mind as a direct solution. If thte performance is not good, you might consider creating some "shadow fields", and writing some triggers to maintain those. Then the overhead of the SUBSTR occurs only when you insert or change a record, and not on the select. For example:

alter table nvz_farms

	add (
		farm_code_num_shadow number,
		farm_code_alpha_shadow char(4)
		);

create trigger X on table nvz_farms
before insert or update
begin

	:farm_code_num_shadow := substr(:farm_code,5,4);
	:farm_code_alpha_shadow := substr(:farm_code,1,4);

end;

You would need to do one mass update to initialize the new fields. Then, you could:

select farm_code_alpha_shadow, max(farm_code_num_shadow) from nvz_farm
group by farm_code_alpha_shadow;

If neccessary, you could index on the shadow columns

regards,

Jonathan

p.s. I'm sure I mutilated the syntax a bit in the above code. Received on Sat Apr 03 1999 - 17:25:53 CST