Select Statement

[schafjoe]

Everybody,
I was wondering this is possible using a Select statement. I am using VFP 7.0. I want to add an Order column to the following Select statement. What I want in the Order column is the ranking of the record.

SELECT Pareto.Commodity, Pareto.Test_Code, ;
SUM(Pareto.TtlQty) AS SumTtlQty ;
FROM Pareto;
GROUP BY Pareto.Commodity, Pareto. Test_Code ;
ORDER BY Pareto.Commodity, SumTtlQty DESC, ;
Pareto.Test_Code

Returned Result
Commodity Test_Code SumTtlQty Order
Monitor 3501-204 13329 1
Monitor 0FUN-017 7405 2
Monitor WKS08 4919 3
Monitor 3501-203 3575 4
Monitor 3501-202 3032 5
Monitor 0FUN-018 2814 6
Monitor 0FUN-023 2426 7
Monitor 3501-218 1071 8
Monitor 3501-219 716 9
Monitor 3501-213 598 10
Motherboard WKS20 74505 1
Motherboard 0FUN-017 9712 2
Motherboard WKS12 2422 3
Motherboard 0FUN-018 2202 4
Motherboard 0FUN-016 1899 5

 

[AirCon]

schafjoe,

What about something like this:

SELECT Pareto.Commodity, Pareto.Test_Code, ;
SUM(Pareto.TtlQty) AS SumTtlQty ;
FROM Pareto;
GROUP BY Pareto.Commodity, Pareto. Test_Code ;
ORDER BY Pareto.Commodity, SumTtlQty DESC, ;
Pareto.Test_Code ;
Into Cursor1

SELECT *, recno() as ORDER from Cursor1

-- AirCon --

 

[AirCon]

I feel stupid arrgghh!!!
My apologies, I misread your message

Well, how about this:


********
Private plFirst, pcCommodity, pnCounter

plFirst = .T.
pcCommodity = ''
pnCounter = 0

SELECT Pareto.Commodity, Pareto.Test_Code, ;
SUM(Pareto.TtlQty) AS SumTtlQty ;
FROM Pareto;
GROUP BY Pareto.Commodity, Pareto. Test_Code ;
ORDER BY Pareto.Commodity, SumTtlQty DESC, ;
Pareto.Test_Code ;
Into Cursor1

Select *, Rank() as Order from Cursor1


Function Rank()
If plFirst
plFirst = .F.
else
If (pcCommodity != Commodity)
pnCounter = 0
pcCommodity = Commodity
endif
pnCounter = pnCounter + 1
endif
Return pnCounter
EndFunc
*******

-- AirCon --

 

[Stella740pl]

schafjoe,

Do you actually need to create the rank with SELECT statement and keep it in the table?

If you are going to use it in a report, you can do it dinamically, by creating a report variable that calculates Count and resets for every new Commodity.
If it is not what you need, then sorry.

Stella.