Bill,
The temp table workaround is just fine. Thanks for scratching your head. Claudine _____ From: [email protected] [mailto:[email protected]] On Behalf Of Bill Downall Sent: Tuesday, April 21, 2009 1:11 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Create Sequence Numbering on the fly 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
