Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Group by correlation

Posted on 2016-08-17
4
Medium Priority
?
74 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

610 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