SELECT group sequential numbering

[torturedmind]

Hi all. I just wanted to share a small tip I found from databasejournal by Gregory A. Larsen. This is based on SQL Server so I modified it a bit to suit my needs coz am (still) using VFP6 . This will sequentially number the records where each group starts numbering from 1 to N, where N is the number of records in the group, and then starts over again from 1, when the next group is encountered.

SELECT COUNT(*) AS itemno, ;
		mytable2.grpcode, ;
		mytable2.membrcode ;
		FROM mytable AS mytable2 ;
		INNER JOIN mytable AS mytable3 ;
		ON mytable2.membrcode >= mytable3.membrcode ;
		AND mytable2.grpcode = mytable3.grpcode ;
		INTO CURSOR mytable4 ;
		GROUP BY mytable2.grpcode, mytable2.grpcode

SELECT mytable.grpcode, ;
	itemno, ;
	mytable.membrcode, ;
	mytable.somenumbr ;
	FROM mytable ;
	INNER JOIN mytable4 ;
	ON mytable.grpcode = mytable4.grpcode ;
	AND mytable.membrcode = mytable4.membrcode ;
	ORDER BY mytable.grpcode, mytable.membrcode

The outcome is something like this:

grpcode     itemno     membrcode    somenumbr
=======     ======     =========    =========
001-000          1        070A01         3845
001-000          2        070A02          548
001-001          1        070A03         6599
002-001          1        070A04         2411
002-001          2        070B01          687
002-001          3        070B03          511
002-001          4        070C05         3247
002-003          1        070B02           91
002-004          1        070C01          508
002-004          2        070C02         4461
002-004          3        070C03          514
002-005          1        070C04         6115
   :             :           :              :
   :             :           :              :
   :             :           :              :

I hope this will also help someone in need. It sure did help me. Credits to Mr. Gregory A. Larsen for the original code.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[cbasoz]

Although you have typo in code yes that is a very nice code. Here is VFP9 version using testdata!Customer table, grouping on Country:

Select t0.*, memberNo ;
	FROM (_samples+'data\customer') t0 ;
	INNER Join ;
	(Select Count(*) As memberNo, t1.Cust_ID ;
	FROM (_samples+'data\customer') t1 ;
	INNER Join (_samples+'data\customer') t2 ;
	ON t1.Cust_ID >= t2.Cust_ID ;
	AND t1.Country = t2.Country ;
	GROUP By t1.Cust_ID,t1.Company,t1.Country) t3 ;
	ON t0.Cust_ID = t3.Cust_ID ;
	ORDER By t0.Country, memberNo

Cetin Basoz
MS Foxpro MVP, MCP

 

[torturedmind]

I should have included NOFILTER in the first SELECT statement like so:

SELECT COUNT(*) AS itemno, ;
        mytable2.grpcode, ;
        mytable2.membrcode ;
        FROM mytable AS mytable2 ;
        INNER JOIN mytable AS mytable3 ;
        ON mytable2.membrcode >= mytable3.membrcode ;
        AND mytable2.grpcode = mytable3.grpcode ;
        INTO CURSOR mytable4 [b][blue]NOFILTER[/blue][/b] ;
        GROUP BY mytable2.grpcode, mytable2.grpcode

This is to avoid the possible error message "<Alias> must be created with SELECT... INTO TABLE." (Thanks Mike Lewis for that tip.)

Although you have typo in code...

Could you please indicate where that typo was? I'm sure I ran this code before posting it here and I never encountered any problem.


kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Mike Lewis]

Kilroy,

Regarding the use of NOFILTER.

This is important in many cases, but in fact won't make any difference here. The filtering only takes effect with single-table queries that are fully optimised and have no fields other than those in the original table. In your case, the presence of the join, the grouping and the COUNT(*) function will ensure that the query won't be done by filtering the input table.

That said, NOFILTER does not harm. And there's a good argument for including it in all your queries as a matter of habit.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

SELECT COUNT(*) AS itemno, ;
        mytable2.grpcode, ;
        mytable2.membrcode ;

        FROM ...

        GROUP BY mytable2.grpcode, mytable2.grpcode

Group by is actually invalid. Due to a bug in VFP7 and earlier you don't see the error. It should be:

SELECT COUNT(*) AS itemno, ;
        mytable2.grpcode, ;
        mytable2.membrcode ;

        FROM ...

        GROUP BY mytable2.grpcode, mytable2.membrcode

Or simpler:

SELECT COUNT(*) AS itemno, ;
        mytable2.membrcode ;

        FROM ...

        GROUP BY mytable2.membrcode

Cetin Basoz
MS Foxpro MVP, MCP

 

[torturedmind]

Oh I see now. Thanks for pointing that out. My bad. My code is different from what I posted sorry. I think my brain was affected by these storms and typhoons

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."