?
Solved

Group by correlation

Posted on 2016-08-17
4
Medium Priority
?
70 Views
Last Modified: 2016-08-17
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
Comment
Question by:jsbx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 20

Accepted Solution

by:
Russ Suter earned 2000 total points
ID: 41759925
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
 

Author Comment

by:jsbx
ID: 41759936
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
 
LVL 41

Expert Comment

by:Sharath
ID: 41760002
Replace Sequence with DTE_Inbox.
Replace NumericValue with Folio.
1
 

Author Closing Comment

by:jsbx
ID: 41760022
Thank you russ it worked perfectly.
Thank you Sharath for the clarification
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question