CODEWITHSUNDEEP
sqlsql-serveroracle-database
Holmes IV
Holmes IV

Reputation: 1747

ROW_NUMBER() Set Null to anything beside Min

So I have this basic setup: 

Declare @temp Table(t1 varchar(1)
                    ,t2 int)

insert into @temp (t1,t2)
Values 
('a','1')
,('a','2')
,('a','3')
,('a','4')
,('a',null)


select t1,t2,ROW_NUMBER() OVER ( PARTITION BY T1 ORDER BY t2) 'rnk'
from @temp

The problem is, the value that is Null get ranked the highest. What I am trying to do is set the first non zero/null value to the highest rank(lowest number) current output is:

t1  t2     rnk
a   NULL    1
a   0       2
a   1       3
a   2       4
a   3       5

I want

t1  t2   rnk
a   NULL 4/5 --either or
a   0    4/5
a   1    1
a   2    2
a   3    3

I know i can do this with subquerys but the problem is to get t2, is a 200 character case statement that i really don't want to copy and paste all over, once to calculate, then one to order by and such. I am seeing a Query to get the values, inside a query to get the rank,inside a query to only pull those ranked 1, which is 3 deep and i don't know like that. note i know it say oracle and i am sure at least one person will mark me down since this is in SQL server, BUT, the actual code is in oracle, i am just much better in SQL server and its easy to translate unless Oracle has some magic function that makes this easier.

Upvotes: 1

Views: 784

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You can use two keys for the order by. The following is compatible with both SQL Server and Oracle:

select t1, t2,
      ROW_NUMBER() OVER (PARTITION BY T1
                         ORDER BY (CASE WHEN t2 IS NOT NULL OR T2 <> 0 THEN 0 ELSE 1 END),
                                  t2
                        ) as rnk
from @temp;

Oracle supports NULLS LAST, which makes it easier: ORDER BY t2 NULLS LAST.

Upvotes: 5

Brian Pressler
Brian Pressler

Reputation: 6713

Another option would be to use the ISNULL function to set it to the max value of the type on null values. So if t2 is an integer it would be:

select t1,t2,ROW_NUMBER() OVER ( PARTITION BY T1 ORDER BY ISNULL(t2,2147483647)) 'rnk'
from @temp

This would prevent you from having to use t2 (your big case statement) in your expression more than once.

I believe Oracle uses NVL instead of ISNULL.

Upvotes: 0

Related Questions