|
> 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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 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.