Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Conditional Row_Number()

    Bit of a strange one that I have yet to come up with an elegant solution to just yet.

    Considering the following dataset:
    Code:
    DECLARE @t table (
       x int
    );
    
    INSERT INTO @t (x)
      VALUES (1), (3), (5), (9), (15), (4), (937), (40);
    I want to give a row number in ascending order for all numbers that are greater than 10.

    Essentially I want the following resultset
    Code:
    x    y
    ---- ----
    1    NULL
    3    NULL
    4    NULL
    5    NULL
    9    NULL
    15   1
    40   2
    937  3
    So far this is what I have rolled with but it just feels like there's a better solution available:
    Code:
    ; WITH cte AS (
      SELECT x
           , CASE WHEN x > 10 THEN 937 END As condition
      FROM   @t
    )
    SELECT x
         , condition
         , CASE WHEN condition = 937 THEN
             Row_Number() OVER (PARTITION BY condition ORDER BY x ASC)
           END As y
    FROM   cte
    ORDER
        BY x
    Any ideas?
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    While it might be a smidgeon more code and unfortunately loses the 937 flavor, I kind of like:
    Code:
    DECLARE @t table (
       x int
    );
    
    INSERT INTO @t (x)
      VALUES (1), (3), (5), (9), (15), (4), (937), (40);
    
    WITH cte AS (
       SELECT x, Row_Number() OVER (ORDER BY x) AS y
          FROM @t
    	  WHERE  10 < x
       UNION SELECT x, NULL
          FROM @t
    	  WHERE  x <= 10
       )
    SELECT *
       FROM cte;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's certainly more elegant.. Will try perf test it today. Thanks Pat!
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two examples. Not tested on Microsoft SQL Server.

    Example 1: A little simpler than original query.
    Code:
    SELECT x
         , CASE
           WHEN x > 10 THEN
                ROW_NUMBER()
                   OVER( ORDER BY CASE
                                  WHEN x > 10 THEN
                                       x
                                  ELSE 2147483647 /* MAX of int Data Type */
                                  END
                       )
           END  AS y
     FROM  @t
     ORDER BY
           x
    ;

    Example 2: More complex.
    But, final ORDER BY is same as ORDER BY in OVER clauses. So, it may be possible to eliminate extra SORT(for final ORDER BY).
    Code:
    SELECT x
         , CASE
           WHEN x > 10 THEN
                r_num - max_x
           END  AS y
     FROM  (SELECT x
                 , ROW_NUMBER() OVER( ORDER BY x ) AS r_num
                 , MAX( CASE
                        WHEN x <= 10 THEN
                             ROW_NUMBER() OVER( ORDER BY x )
                        ELSE 0
                        END
                      ) OVER() AS max_x
             FROM  @t
           ) AS s
     ORDER BY
           x
    ;

Posting Permissions

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