Re: Possible use of a Cursor

From: Dieter Nöth <dnoeth_at_gmx.de>
Date: Wed, 23 Feb 2005 13:34:11 +0100
Message-ID: <383bj8F5jkcb0U1@individual.net>

Paul Izzo wrote:

> Remember I'm looking for the first step in an "INSTRUCTION" that has
> "MACHINE_PROC" 3600, 3400 or 3430
>
> Example 1
> INSTRUCTION_NR | MACHINE_PROC | POSITION_NR
> 123456 | 3600 | 10
> 123456 | 5600 | 20
> 123456 | 4587 | 30
>
> Example 2
> INSTRUCTION_NR | MACHINE_PROC | POSITION_NR
> 456789 | 3600 | 5
> 456789 | 6522 | 10
> 456789 | 7841 | 20

select * from tab t1
where position_nr =
  (select min(position_nr))
   from tab t2
   where t1.instruction_nr = t2.instruction_nr   )
and machine_proc in (3600, 3400, 3430)

or if your version already supports OLAP funtions:

select * from
  (select

     instruction_nr
     ,machine_proc
     ,position_nr
     ,row_number() over (partition by instruction_nr
        order by postition_nr) rn

  ) dt
where rn = 1
and machine_proc in (3600, 3400, 3430)

Dieter Received on Wed Feb 23 2005 - 06:34:11 CST