Re: sorting in oracle?

From: Chris Kruiper <c.a.kruiper_at_kpn-telecom.nl>
Date: 13 Aug 1998 12:29:21 GMT
Message-ID: <01bdc6b6$003b0f60$332d15ac@tonta000067>

Hi,

I'm not totally sure what you want to do, but it looks that you want to set c = 1 to the highest value of b and 2 to the value after the highest......

What you could do....write pl/sql....something like this (maybe some little mistakes in writing etc......):

declare
  p_counter number := 0;

  cursor c_cursor
  is
  select distinct(a)
  from T;
  r_cursor c_cursor%rowtype;

  cursor c_cursor1
 (p_a t.a%type)
  is
  select distinct (b)
  from T
  where a = p_a
  order by b desc;
  r_cursor1 c_cursor1%rowtype;

begin
  open c_cursor;
  fetch c_cursor into r_cursor;
  while c_cursor%found loop
    open c_cursor1(r_cursor.a);
    fetch c_cursor1 into r_cursor1;
    while c_cursor1%found then

      loop
        p_counter := p_counter + 1;
        update T set c = p_counter
        where a = r_cursor.a
        and    b = r_cursor1.b;
        fetch c_cursor1 into r_cursor1;
     end loop;

   fetch c_cursor into r_cursor;
   end loop;
end;

Hope you can use it,

Chris

Iancrozier <iancrozier_at_aol.com> schreef in artikel <1998081214123000.KAA03824_at_ladder01.news.aol.com>...
> I have a need to do the following
> In table T, I have three columns A, B, and C
> B has several numeric values for each value of A, some times the same
value
> e.g.
> A=1234, B=200,200,300,400,600,600
>
> What I need to do is the following ( in this example )
> where B=600, set C=1
> where B=400, set C=2
> where B=300, set C=3
> where B=200, set C=4
>
> There are many values of B, so I have to do a comparison of different
values of
> B for every value of A. Can I do this in SQL?
>
> Thanks in advance.
>
>
>
Received on Thu Aug 13 1998 - 07:29:21 CDT