How do I return count of consecutive values in an ordered table?

[Roverboy]

Good Evening,

The following is a subset of a table I have in an MS SQL 2008 DB. I am trying to output to a table, the Serial Number , the LID with the greatest count of consecutive LID values, and the actual count. Important to note that the table is ordered by the Last Updated Date value descending (this condition is critical). It can be grouped by Serial Number or ordered by Serial Number ascending or descending...whatever is more efficient and makes sense. Here's what the data looks like:

[Serial Number] [LID] [Last Updated Date]

123456 AAA 2012-09-24

123456 AAA 2012-09-23

123456 AAA 2012-09-22

123456 AAA 2012-09-21

123456 BBB 2012-09-20

123456 BBB 2012-09-19

123456 AAA 2012-09-18

123456 AAA 2012-09-17

123456 AAA 2012-09-16

234567 BBB 2012-09-24

234567 BBB 2012-09-23

234567 AAA 2012-09-22

The desired output to the table is:

[Serial Number] [LID] [LID Count]

123456 AAA 4

234567 BBB 2

I am at a loss. I've tried using ROW_NUMBER() OVER(PARTITION BY [Service Tag], [LID] ORDER BY [Last Updated Date] DESC ) but all that does is break up my descending date order and I end up with the count and the LID that occurs the most during the range of dates.

Thanks in advance for any assistance you can provide!

Best Regards!

[Rabbit]

I can see it working with a very convoluted query in which you join the table to itself to get the row before the next break in LID values and then join to a itself a third time to do a ranking query, but SQL isn't really optimized for this kind of query.

You may be better off using a cursor so that you only have to make one pass through the records.