Sorry, I should have included the results: a1.EmpID a1.EmpFNam a1.EmpLname
a1.NetAmount Sales_Rank
---------- ---------- ---------------- --------------- ----------
110 Sam Donald $155,884.00 1
104 Peter Coffin $140,687.30 2
102 Ernest Hernandez $87,101.00 3
109 John Minyo II $65,173.00 4
105 John Smith $64,244.45 5
111 Joe Donohoe $40,414.25 6
103 Mary Simpson $34,533.00 7
106 Darnell Williams $33,511.50 8
115 Craig Alan $22,531.50 9
101 June Wilson $18,787.50 10
107 John Chow $13,290.50 11
108 Jane Sullivan $10,021.50 12
On Tue, Apr 21, 2009 at 11:10 AM, Bill Downall <
[email protected]> wrote:
> 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
>>
>>
>>
>>
>>
>
>
