RE: Can you do this in SQL

> From: "Alan Novick" <mida@pressmantoy.com>
>
> > 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?

> From: R. Bruce Hoffman, Jr.
>
> 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."

Even if you COULD make this statement work, why would you want to?  The idea
of adding the max(rank) subselect is just bad from every possible aspect.
It's not particularly readable, and even if it is, it's additional overhead
that's simply not needed.  The idea of adding extra I/O just because you
want to use SQL is a really poor idea.  This same thing could be done using
an OPQNRYF and a four-line RPG program.

Input Primary F-spec
add i to counter
move counter to rank field
update record

This unreasonable desire to use SQL even where it's a really bad fit is
going to continue to cause system bloat.  If you don't know RPG, learn it.
The syntax is pretty darned simple (especially compared to SQL!) and it's an
incredibly powerful tool.

Finally, there IS a way to do this with SQL, though it takes two steps.
First, use a SELECT with ORDER BY to create a work file in the correct
sequence.  Then, update your field using an UPDATE MYFILE SET RANK =
RRN(MYFILE).  Here, of course, you have the overhead of creating a duplicate
of the file, but hey, if you aren't worried about performance, this is just
as valid a technique as any.

Joe