You can use an intermediate cursor, e.g.
select min(Vs) as MinVs, MDate, COUNT(*) as cntDups ;
from myTable ;
GROUP BY MDate ;
HAVING COUNT(*) > 1 into cursor curDups NOFILTER
SELECT T.*, IIF(T.Vs = Dup.MinVs, 1, 0) as KeepInfo, Dup.cntDups ;
from MyTable T INNER JOIN curDups Dup ON T.MDate = Dups.MDate into cursor curFinal nofilter
In this case KeepInfo will tell you if you need to print or not. The resulting cursor will only contain records where you have duplicate dates. If you need all records from your table, use a LEFT JOIN instead of the INNER JOIN and change condition to Dup.mDate IS NULL or T.Vs = Dup.MinVs.
>>>>>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
If it's not broken, fix it until it is.
My Blog