skip null or 0 value while using row_number sql server

Question

i am trying to get row_number from the data, but i want to skip the null or 0 value

DECLARE @ProcessId INT = 6013006
DECLARE @CommHeaderId int

SELECT  @CommHeaderId = a.id
FROM    EprocUltimate.dbo.commercial_header a 
WHERE   a.process_id = @ProcessId

SELECT  b.rank_quotation, c.rank_nego,
        CASE ISNULL(a.quotation_usd, 0) WHEN 0 THEN a.quotation_idr ELSE a.quotation_usd END As  Quotation,
        CASE ISNULL(a.nego_usd, 0) WHEN 0 THEN a.nego_idr ELSE a.nego_usd END As Nego
FROM    EprocUltimate.dbo.commercial_vendor a
        INNER JOIN 
        (
            SELECT  a.id, ROW_NUMBER() OVER(ORDER BY CASE quotation_usd WHEN 0 THEN quotation_idr ELSE quotation_usd END ASC) AS rank_quotation
            FROM    EprocUltimate.dbo.commercial_vendor a
            WHERE   a.commercial_header_id = @CommHeaderId AND a.commercial_admin_bid_evaluation_result = 'Pass'
        ) b ON a.id = b.id
        INNER JOIN
        (
            SELECT  a.id, ROW_NUMBER() OVER(ORDER BY CASE ISNULL(nego_usd, 0) WHEN 0 THEN nego_idr ELSE nego_usd END ASC) AS rank_nego          
            FROM    EprocUltimate.dbo.commercial_vendor a
            WHERE   a.commercial_header_id = @CommHeaderId AND a.commercial_admin_bid_evaluation_result = 'Pass'
        ) c ON a.id = c.id
WHERE   a.commercial_header_id = @CommHeaderId AND a.commercial_admin_bid_evaluation_result = 'Pass'
ORDER BY b.rank_quotation

heres the result

rank_quotation rank_nego, value1,       value2
1                  3      775460000.00  770000000.00
2                  1      781036525.00  NULL
3                  2      786250000.00  NULL

from this query what i want is to get row_numbering the rank_nego with having value first

so the result that i want to achive is

rank_quotation rank_nego, value1,       value2
1                  1      775460000.00  770000000.00
2                  2      781036525.00  NULL
3                  3      786250000.00  NULL

You question contains no table schema, no sample data, and deals with non-trivial code. Please have a read through How to create a Minimal, Reproducible Example. — AlwaysLearning
Your query seems way more complicated than necessary. You might want to ask another question, explaining what the code is supposed to be doing and providing sample data and desired results. — Gordon Linoff

Olivier Depriester Olivier Depriester · Accepted Answer · 2020-09-11T07:58:34

You can use a little workaround to have your NULL values as last records in your subquery with the ROW_NUMBER() OVER (ORDER BY ...) as rank_nego

(
    SELECT  a.id, ROW_NUMBER() OVER(ORDER BY 
       -- This statement will first sort records having nego_usd and nego_idr as NULL/0 at the end of the list 
       CASE WHEN ISNULL(nego_usd, 0) = 0 AND ISNULL(nego_idr, 0) = 0 THEN 1 ELSE 0 END ASC,
       -- and then sort with the real values
       CASE ISNULL(nego_usd, 0) WHEN 0 THEN nego_idr ELSE nego_usd END ASC
    ) AS rank_nego
    FROM    EprocUltimate.dbo.commercial_vendor a
    WHERE   a.commercial_header_id = @CommHeaderId AND a.commercial_admin_bid_evaluation_result = 'Pass'
) c ON a.id = c.id

skip null or 0 value while using row_number sql server

1 Answers