>>>>Hi, Is is possible to use an sql statement(s) to count and number duplicates? The original table looks something like this, showing an excerpt where there are three entries for one date:
>>>>
>>>>

>>>>Mdate            Vs
>>>>05/28/2013     v1
>>>>05/28/2013     v1
>>>>05/28/2013     v1
>>>>

>>>>
>>>>The following is a start in that it will give me a table that shows how many duplicates exist for each date.
>>>>
>>>>

>>>>SELECT mdate,COUNT(mdate) FROM ch5 ;
>>>>    INTO TABLE dups;
>>>>    GROUP BY mdate ;
>>>>    HAVING COUNT(mdate) > 1
>>>>

>>>>
>>>>Here is a part of the report that the above produces:
>>>>
>>>>

>>>>mdate         cnt_mdate
>>>>05/28/2013   3
>>>>08/28/2013   2
>>>>

>>>>
>>>>And by using set relationship and a do while loop, I can count and label the duplicates. But I was just wondering if an SQL statement(s) could do it all? The final output that I need is::
>>>>
>>>>

>>>>Mdate            cnt/date    Vs
>>>>05/28/2013     1              v1
>>>>05/28/2013     2
>>>>05/28/2013     3
>>>>

>>>>
>>>>Thanks,
>>>>Steve
>>>
>>>To add sequential numbers to records, read the section "Including record numbers in results" in http://www.tomorrowssolutionsllc.com/Conference%20Sessions/Solving%20Common%20Problems%20with%20VFP%27s%20SQL.pdf. Basically, you use one query to collect the results and another to add RECNO() to the field list.
>>>
>>>Tamar
>>
>>Thank you! The part you refer to is on p. 19. But I don't want to insert recno()'s per se. (I shouldn't have skipped at step.) I wondered if I could number the instances where there are multiple occurances for a given date, perhaps resulting in a numbering schema as shown in cnt/date or new_index; because, once I see something like this:
>>
>>

>>Mdate            cnt/date            new_index            Vs
>>05/28/2013        1                   2013052801         v1
>>05/28/2013        2                   2013052802         v2
>>05/28/2013        3                   2013052803         v3
>>

>>
>>I need to blank out all but the first of the multiple Vs's for a given date, for a final report that will look like this:
>>
>>

>>Mdate            cnt/date            Vs
>>05/28/2013         1                   v1
>>05/28/2013         2
>>05/28/2013         3
>>

>
>Ah, yeah, what I missed was numbering within each group, once you've selected the records of interest. I see that you're using SQL Server, though, so in fact, this is easy using OVER and the ROW_NUMBER function. Check out this article on my site: http://www.tomorrowssolutionsllc.com/Articles/Getting%20the%20Top%20N%20for%20each%20Group.pdf, in particular, the part that begins with "The SQL Server Solution."
>
>Tamar

Again, my mistake, I should have indicated that am using solamente VFP 9.0 SP2. Do I need SQL server to do this? If so, I will shift. I am just looking for a simpler solution than coding everything with loops in VFP. Thanks again. Steve