By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,964 Members | 1,810 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,964 IT Pros & Developers. It's quick & easy.

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

P: 1
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!
Sep 27 '12 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,430
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.
Sep 27 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.