开发者

SQL Incremenet and Reset Variable Insert

I have probably missed something simple with my problem. However its like me to overlook any small details. But I have开发者_如何学JAVA been searching for a while now and havent come across anything similar to my issue.

Setup: SQL 2005, Stored Procedure.

I have a table that is updated frequently with call attempts. Using a UNIQUEIDENTIFIER to tie all the records together i.e.

xxx-xxx-xxx-xxx-xxxx | 20/06/2011 12:00 | 10

I want to have a stored procedure that Grabs all the records, and sorts them by the UNIQUEIDENTIFIER and at the same time, producing a counter for the attempts. i.e.

1111-1111-1111-1111    | 20/06/2011 12:05    |  10 | 0    
1111-1111-1111-1111    | 20/06/2011 12:06    |  30 | 1   
2222-2222-2222-2222    | 20/06/2011 12:10    | 120 | 0  
3333-3333-3333-3333    | 20/06/2011 12:20    |  50 | 0

From the above it should be simple to be able to to indentify the call attempts and add on the number. However im probably being very silly.

Any help is appreciated.

Regards Chris


You can use ROW_NUMBER.

e.g.

SELECT ID, DateField, FieldA, 
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DateField ASC) AS Counter
FROM YourTable
ORDER BY ID, DateField

The PARTITION BY basically resets the counter for each distinct ID and the following ORDER BY ensures the counter is assigned incrementally ordered by the Date field. Note this will be a counter starting from 1 each time. If you want it to start from 0, you can just subtract 1 in the SELECT


Could you not use the "group by" clause (with a count) in SQL ?

You could use "order by" to perform the sort (although its not immediately apparent how SQL implements that or what use it would be).

Also, the question title doesn't seem to match what you're asking.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜