Solved

Group by correlation

Posted on 2016-08-17
4
65 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 500 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…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

739 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