Re: Can you do this in SQL

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

as an aside....

update ranktest
  set rank = (select max(rank) + 1 from ranktest)
  where dollar = (select max(dollar) from ranktest
    where rank = 0)
    and rank = 0

if you repeatedly execute this, you get the ranking, but you have to repeat
the execution of the statement until no more records are updated.... sqlcode
<> 0


===========================================================
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: "R. Bruce Hoffman, Jr." <rbruceh@attglobal.net>
To: <midrange-l@midrange.com>
Sent: Tuesday, January 29, 2002 3:54 PM
Subject: Re: Can you do this in SQL


> 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.
> >
>
> _______________________________________________
> 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.
>