Re: Can you do this in SQL

["R. Bruce Hoffman, Jr." <rbruceh@xxxxxxxxxxxxx>]

hhhhmmmmmmmmm......

with inRank (cust, doll) as (select customer, dollars from rankingFile order
by dollars desc)
update rankingFile
  set rank  = (select max(rank) + 1 from rankingFile)
  where customer = cust
       and rank = 0

it's just too bad that SQL chokes on this... the 400 won't allow order by on
the common table expression and the 400 won't do the recursion and it won't
allow update from the common table expression.

so the short answer appears to be "no, not with just SQL."

===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified Specialist - iSeries Administrator
 -- IBM Certified Specialist - RPG IV Developer

"Suppose you were an idiot...
  And suppose you were a member of Congress...
  But I repeat myself."
    - Mark Twain

----- Original Message -----
From: "Alan Novick" <mida@pressmantoy.com>
To: "MidRange-L" <MidRange-L@MidRange.Com>
Sent: Tuesday, January 29, 2002 1:45 PM
Subject: Can you do this in SQL


> Lets say I have a file with three fields, customer number, dollars sold,
and
> rank.  I can fill the customer number and dollars sold fields from my
files.
> I then one to put a number in the rank field, so that the customer who
> bought the most gets a ranking of one, the one who bought less gets 2,
etc.
> Is there a way to do this in sql?
>
> Thanks,
>
> Alan Novick
> Pressman Toy Corporation
> New Brunswick, NJ
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>