Re: can this be done more elegant?

willemreinders_at_gmail.com wrote:
> I want to select from a test ride table the data of the last Right
> Curve. I can do that by the select as given below. My problem is that
> this select is quite inefficient, because the same basic select is
> performed twice (once to obtain the time tag and once to obtain the
> measurement data).
> In cases of heavily joined, more complex selects the same select has to
> be repeated almost exactly. I have the feeling that this can be solved
> more elegant. Any idea's?
>
> create table test_ride (
> time number,
> event_type varchar2(10),
> speed number
> );
> --
> insert into test_ride values ( 1.20, 'curve_L', 90.2);
> insert into test_ride values (11.45, 'curve_R', 89.6);
> insert into test_ride values (21.80, 'brake' , 102.2);
> insert into test_ride values (21.98, 'curve_R', 79.3);
> insert into test_ride values (41.99, 'curve_R', 60.1);
> insert into test_ride values (41.03, 'curve_L', 69.7);
> insert into test_ride values (51.32, 'curve_R', 122.3);
> insert into test_ride values (51.20, 'curve_L', 128.9);
> insert into test_ride values (51.65, 'brake' , 93.1);
> --
> select *
> from test_ride
> where time = (
> select MAX(time)
> from test_ride
> where event_type = 'curve_R'
> )
> and event_type = 'curve_R'
> ;

Look at using a WITH query.

Morgan's Library at www.psoug.org. Click on 'With Clause'.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)