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

Adrian:

Try...

(1) Create an index on farm_code
(2) change sql to:

select max(substr(farm_code,5,4))max_num from nvz_farms where farm_code like 'BRID%';

This will result in an index range scan instead of a full table scan.

Greg

In article <37058743.149349833_at_news.globalnet.co.uk>,   adrianh_at_globalnet.co.uk (Adrian Harrison) wrote:
> I have a varchar2 field called farm_code with data held in the
form "xxxx9999".
>
> For a group of records that have the same "XXXX" part I need a SELECT
statement that returns the
> highest row based on the "9999" value.
>
> I have a statement that seems to work fine but would like to know if this is
the best/quickest way
> of achieving this -
>
> select max(substr(farm_code,5,4))max_num from nvz_farms where substr
(farm_code,1,4) = 'BRID';
>
> Any thoughts?
>
> Thanks in advance!
>
> Adrian Harrison
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Apr 02 1999 - 09:21:43 CST