[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 76
  • Last Modified:

Group by correlation

Hello,

Can you help me with this query please (ms sql 2005):

I have a table like this:

NUMBER
1
2
3
5
6
8
9
10
20
21
25
28
30
31

I want to get this:

START       END
1           3
5           6
8           10
20          21
25          25
28          28
30          31

thanks.
0
jsbx
Asked:
jsbx
  • 2
1 Solution
 
Russ SuterCommented:
Something like this...
WITH T AS (
              SELECT
                ROW_NUMBER() OVER (ORDER BY NumericValue) - NumericValue AS Grp,
                NumericValue
              FROM
                Sequence
             )
    SELECT
        MIN(NumericValue) AS [From],
        MAX(NumericValue) AS [To]
    FROM
        T
    GROUP BY
        Grp
    ORDER BY
        MIN(NumericValue)

Open in new window

0
 
jsbxAuthor Commented:
Sorry Russ, I shopuld have posted my real table names since i dont know how to translate your example to my actual table:

my table is called DTE_Inbox
and the column with the numbers is named Folio and it's the 5th column from left to right
0
 
SharathData EngineerCommented:
Replace Sequence with DTE_Inbox.
Replace NumericValue with Folio.
1
 
jsbxAuthor Commented:
Thank you russ it worked perfectly.
Thank you Sharath for the clarification
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now