1. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445

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?

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

3. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
That's certainly more elegant.. Will try perf test it today. Thanks Pat!

4. Registered User
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
•