Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Unanswered: Select clause RANGE

    Hi, I ahave a table as follows:


    col1 col2
    -----------------
    1 1
    2 1
    3 1
    5 1
    6 2
    10 2
    11 2

    How can i wtire a query which would produce following output:

    resCol1 resCol2
    ---------------
    1-3 1
    5 1
    6 2
    10-11 2

    The only option coming to my mind is to use Cursor and temporary table. The problem is I need to do this operation over 1 million records.

    Can someone suggest some better alternative ?

    Thanks
    Niladri

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    your requirements are not clear but I've given it a stab
    Code:
    DECLARE @t table (
       col1 int
     , col2 int
    )
    
    INSERT INTO @t (col1, col2) VALUES(1, 1)
    INSERT INTO @t (col1, col2) VALUES(2, 1)
    INSERT INTO @t (col1, col2) VALUES(3, 1)
    INSERT INTO @t (col1, col2) VALUES(5, 1)
    INSERT INTO @t (col1, col2) VALUES(6, 2)
    INSERT INTO @t (col1, col2) VALUES(10, 2)
    INSERT INTO @t (col1, col2) VALUES(11, 2)
    
    SELECT x.the_min
         , y.the_max
         , x.value
         , Convert(varchar(11), x.the_min) + Coalesce('-' + Convert(varchar(11), NullIf(y.the_max, x.the_min)), '') As resCol1
         , x.value As resCol2
    FROM   (
            SELECT a.col1 As the_min
                 , a.col2 As value
                 , Row_Number() OVER (ORDER BY a.col1) As seq
            FROM   @t As a
             LEFT
              JOIN @t As b
                ON b.col2 = a.col2
               AND b.col1 + 1 = a.col1
            WHERE  b.col1 IS NULL
           ) As x
     INNER
      JOIN (
            SELECT a.col1 As the_max
                 , Row_Number() OVER (ORDER BY a.col1) As seq
            FROM   @t As a
             LEFT
              JOIN @t As b
                ON b.col2 = a.col2
               AND b.col1 - 1 = a.col1
            WHERE  b.col1 IS NULL
           ) As y
        ON y.seq = x.seq
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •