Solved

Group by correlation

Posted on 2016-08-17
4
43 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
  • 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 40

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server Trigger for INSERT, UPDATE is not working 9 47
Mssql SQL query 14 28
Sql query to Stored Procedure 6 21
Azure SQL DB? 3 22
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now