Group by not working with cursor

Question

consider the following query. I have created a cursor for this query. My Problem is group by not working properly in the cursor.but when i execute the query it gives perfect group by result.

when I iterate cursor using FETCH NEXT and WHILE loop it gives more than one group for the same point_id.

Can you help me to resolve this problem ??? thanks in advance..

select timestamp as 'TS',REPLACE(REPLACE(POINT_ID,'[','_'),']','_') AS POINT_ID,_VAL 
from TCF1_PULLCORD 
where timestamp between '11/01/2011 6:30:00 AM' and '12/01/2011 6:29:59 AM'
group by point_id,timestamp,_val


DECLARE MYCUR CURSOR 
FOR
    select timestamp as 'TS',REPLACE(REPLACE(POINT_ID,'[','_'),']','_') AS POINT_ID,_VAL 
    from TCF1_PULLCORD 
    where timestamp between '11/01/2011 6:30:00 AM' and '12/01/2011 6:29:59 AM'
    group by point_id,timestamp,_val

Answer 1

This is your database's way of saying "Please don't use a cursor on me." :D

Just kidding, but in all seriousness you will find better performance iterating through a table variable than using a cursor.

DECLARE @timestamps TABLE( 
    TS DATETIME,
    POINT_ID VARCHAR(100),
    _VAL VARCHAR(100)
    )
DECLARE @currTimeStamp DATETIME 

INSERT INTO @timestamps 
select timestamp as 'TS',REPLACE(REPLACE(POINT_ID,'[','_'),']','_') AS POINT_ID,_VAL 
from TCF1_PULLCORD 
where timestamp between '11/01/2011 6:30:00 AM' and '12/01/2011 6:29:59 AM'
group by point_id,timestamp,_val


WHILE (SELECT COUNT(TS) FROM @timestamps > 0)
BEGIN
    SELECT @currTimeStamp = MIN(TS) FROM @timestamps
    --Do work here
    ...

    --Delete the timestamp we just worked on
    DELETE FROM @timestamps WHERE TS = @currTimeStamp
END 

In addition, unless your SELECT is doing other things we can't see here, the GROUP BY should be unnecessary. If you're not doing any aggregate functions (e.g. SUM, MAX, MIN) and you're really just trying to get the unique combinations, then SELECT DISTINCT is a more efficient way to get there.

In that case, your select would be:

select DISTINCT timestamp as 'TS',REPLACE(REPLACE(POINT_ID,'[','_'),']','_') AS POINT_ID,_VAL 
from TCF1_PULLCORD 
where timestamp between '11/01/2011 6:30:00 AM' and '12/01/2011 6:29:59 AM'