Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Select Max to Mini last 12 month
Message
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
01157837
Message ID:
01158120
Views:
22
Actually it has to be this:
Select yearmonth, count(*) as Rank, 000 AS MaxMiniMonth from mytable group by yearmonth ORDER BY 2 DESC INTO CURSOR Result READWRITE
lnRecs = RECCOUNT()+1
REPLACE MaxMiniMonth WITH m.lnRecs-RecNo() ALL
>Yes, this will works, but you the ORDER must be BY first field. Also we don't knwo what version of VFP Paul use. If He use VFP7 or smaller He doesn't have READWRITE option to SELECT. I don't know what you didn't like in my suggestion, sooo many functions and calculations are involved :o))) (just joke). Also it is easier to:
>
>REPLACE MaxMiniMonth WITH RECOUNT()-RecNo()+1 ALL
>** or faster
>lnRecs = RECCOUNT()+1
>REPLACE MaxMiniMonth WITH m.lnRecs-RecNo() ALL
>
>
>
>
>
>>Ok then this will
>>
>>
>>Select yearmonth, 000 AS MaxMiniMonth from mytable group by yearmonth ORDER BY 2 DESC INTO CURSOR Result REARWRITE
>>lnRecs = RECCOUNT("Result")
>>SCAN
>>   REPLACE MaxMiniMonth WITH lnRecs
>>   knRecs = lnRecs - 1
>>ENDSCAN
>>
>>
>>
>>>Sorry Jim. This won't works. It would give you the following result:
>>>
>>>YearMonth     MinMax
>>>---------------------
>>>200609            2
>>>200608            2
>>>....
>>>
>>>
>>>
>>>
>>>>
>>>>Select yearmonth, COUNT(*) AS MaxMiniMonth from mytable group by yearmonth ORDER BY 2 DESC
>>>>
>>>>
>>>>
>>>>>Hi all,
>>>>>
>>>>>I have mytable :
>>>>>
>>>>>mytable.dbf
>>>>>
>>>>>YearMonth ( character field )
>>>>>
>>>>>200609
>>>>>200609
>>>>>200608
>>>>>200608
>>>>>200607
>>>>>200607
>>>>>200606
>>>>>200606
>>>>>200605
>>>>>200605
>>>>>200604
>>>>>200604
>>>>>200603
>>>>>200603
>>>>>200602
>>>>>200602
>>>>>200601
>>>>>200601
>>>>>200512
>>>>>200512
>>>>>200511
>>>>>200511
>>>>>200510
>>>>>200510
>>>>>200509
>>>>>200509
>>>>>200508
>>>>>200508
>>>>>200507
>>>>>200507
>>>>>200506
>>>>>200506
>>>>>
>>>>>to get only last 12 months :
>>>>>
>>>>>newtable ( 2 fields )
>>>>>
>>>>>yearmonth MaxMiniMonth
>>>>>200609 12
>>>>>200608 11
>>>>>200607 10
>>>>>200606 9
>>>>>200605 8
>>>>>200604 7
>>>>>200603 6
>>>>>200602 5
>>>>>200601 4
>>>>>200512 3
>>>>>200511 2
>>>>>200510 1
>>>>>
>>>>>Select yearmonth from mytable group by yearmonth
>>>>>
>>>>>How to get MaxMiniMonth in my select SQL ?
>>>>>
>>>>>TIA
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform