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,
> rank.  I can fill the customer number and dollars sold fields from my
> 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,
> 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
> 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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.