I think Claudine was looking for something like this: -- set it up in RRBYWxx create view SalesSumByEmployee + (EmpLName, EmpFName, EmpID, NetAmount) + AS SELECT EmpLName, EmpFName, e1.EmpID, sum (i2.netAmount) + FROM Employee e1, InvoiceHeader i2 + WHERE (e1.EmpID = i2.EmpID) + GROUP BY EmpLName, EmpFName, e1.EmpID
-- do the query SELECT a1.EmpID, a1.EmpFName, a1.EmpLname, a1.NetAmount, COUNT(a2.EmpID) AS `Sales_Rank` + FROM SalesSumByEmployee a1, SalesSumByEmployee a2 + WHERE a1.NetAmount <= a2.NetAmount or (a1.NetAmount=a2.NetAmount and a1.EmpID = a2.EmpID) + GROUP BY a1.EmpID, a1.EmpFName, a1.EmpLname, a1.NetAmount + ORDER BY a1.NetAmount DESC, a1.EmpLN DESC Bill On Tue, Apr 21, 2009 at 10:49 AM, Dennis McGrath <[email protected]>wrote: > Claudine, > > > > I really couldn’t think of any other way the computer could come up with > the right answer. > > I think I have had to do this just one time before and gave in to the > simple solution after much head scratching. > > > > Dennis > > > ------------------------------ > > *From:* [email protected] [mailto:[email protected]] *On Behalf Of *Claudine > Robbins > *Sent:* Tuesday, April 21, 2009 12:40 PM > > *To:* RBASE-L Mailing List > *Subject:* [RBASE-L] - Re: Create Sequence Numbering on the fly > > > > Dennis, > > > > Apparently you came to the same conclusion long before I did. > > > > Claudine** > > > ------------------------------ > > *From:* [email protected] [mailto:[email protected]] *On Behalf Of *Dennis > McGrath > *Sent:* Tuesday, April 21, 2009 12:38 PM > *To:* RBASE-L Mailing List > *Subject:* [RBASE-L] - Re: Create Sequence Numbering on the fly > > > > Why not create a temp table with an integer column and a text column > > Insert the text data into the table in the correct order and then > autonumber the integer column. > > Use this table as the basis for your menu, no further sorting necessary. > > > > Dennis McGrath > > > ------------------------------ > > *From:* [email protected] [mailto:[email protected]] *On Behalf Of *Claudine > Robbins > *Sent:* Tuesday, April 21, 2009 12:26 PM > *To:* RBASE-L Mailing List > *Subject:* [RBASE-L] - Re: Create Sequence Numbering on the fly > > > > Bill, > > > > Thank you for your quick response. > > > > I have a simple view of one text field in a table with a where clause and > I’d like for the result to read > > 1 – The birds are on the feeder > > 2 – The squirrels are chasing each other > > 3 – The cat is sleeping > > Etc… > > > > I seem to remember that you had a select (perhaps even with a union to a > dummy table) in order to do this that’s why I tried to look it up in ADVSQL. > > > > Claudine** > > > ------------------------------ > > *From:* [email protected] [mailto:[email protected]] *On Behalf Of *Bill > Downall > *Sent:* Tuesday, April 21, 2009 12:13 PM > *To:* RBASE-L Mailing List > *Subject:* [RBASE-L] - Re: Create Sequence Numbering on the fly > > > > Claudine, > > > > I'm listening, but not remembering completely. And my ancient copy of that > book is 1500 miles away from me. What exactly do you want to do? See > sequence numbers on a SELECT from a table that doesn't have sequence > numbers? > > > > Bill > > On Tue, Apr 21, 2009 at 9:45 AM, Claudine Robbins < > [email protected]> wrote: > > With a plug for Bill Downall and David Blocker’s excellent “Advanced SQL” > book which, to this day, remains a very valuable resource and is not > routinely included in the resources for R:BASE… I’ve searched for an hour, > both in the book and in my email Rbase-l archives… > > > > I’m looking for that special UNION statement (Bill, are you there?) to > create a sequence numbering on the fly. > > > > TIA, > > > > Claudine > > > > >
