Multiple ways to order in Ranking functions SQL Server 2014

Question

LN_NUMBER       RUN_DATE    SALE_DATE   STATUS_CODE TYPE    PROCESSOR_ID    
------------------------------------------------------------------------
0201203909      02/21/17    09/30/15        R   1      TG1          
0201203909      02/21/17                    R   2      TG1      
0201203909      02/21/17                    A   1      MW1  -------choose this row for LN_number 0201203909   
0201105919      02/21/17    08/24/16        S   2      MW1  ---choose this row for LN_number 0201105919
0201105919      02/21/17    08/24/16        S   2      MW1  
0201105919      02/21/17    07/01/15        R   2      TG1  
1400000138      02/21/17-----------------------------------------------------------Choose this for LN_NUMBER 1400000138  

I have a table named closure shown above. I need to select the records from the closure table .

The condition is this:

• If there is a Status code=A then choose that row (Make it as RowNum=1).
• If Status code is not A, then choose the record with most recent SALE_DATE(Make it as RowNum=1).

I tried this code

SELECT
    [LN_NUMBER],
    [RUN_DATE] , 
    [SALE_DATE], 
    [STATUS_CODE], 
    [TYPE],
    [PROCESSOR_ID],
    ROW_NUMBER() OVER (partition by [LN_NUMBER]  order by 
                     case when [STATUS_CODE]='A' then [STATUS_CODE] end asc,
                     case when [STATUS_CODE]!='A' then CONVERT(DATE,[SALE_DATE]) end desc)               
              as 'RowNum' 
FROM [dbo].[Closure ] 

This code is working fine for LN_NUMBER =0201105919. But For LN_NUMBER=0201203909, I am not getting the rowNum in the required way.

This is what I am getting the result:

LN_NUMBER   RUN_DATE    SALE_DATE   STATUS_CODE TYPE PROCESSOR_ID   RowNum
0201203909   02/21/17   09/30/15     R          1     TG1           1
0201203909   02/21/17                R          2     TG1           2
0201203909   02/21/17                A          1     MW1           3
0201105919   02/21/17   08/24/16     S         2      MW1           1
0201105919   02/21/17   08/24/16     S         2      MW1           2
0201105919   02/21/17   07/01/15     R         2      TG1           3

Could anyone please help me on this.I am using SQL server 2014. Thank you very much for your time and help

Answer 1

select  
  [LN_NUMBER],
  [RUN_DATE]  ,  
  [SALE_DATE],  
  [STATUS_CODE],  
  [TYPE],
  [PROCESSOR_ID],
  ROW_NUMBER () over (
    partition by [LN_NUMBER] 
    order by case when [STATUS_CODE]='A' then 0 else 1 end asc
      , CONVERT(DATE,[SALE_DATE]) desc
  )  as  'RowNum'  
FROM  [dbo].[Closure]  

---

test setup: http://rextester.com/FNRE71956

create table Closure ( 
    LN_NUMBER varchar(10)
  , RUN_DATE  varchar(10)
  , SALE_DATE varchar(10)
  , STATUS_CODE char(1)
  , TYPE int
  , PROCESSOR_ID char(3)
)
insert into t values
 ('0201203909','02/21/17','09/30/15','R',1,'TG1')
,('0201203909','02/21/17','        ','R',2,'TG1')
,('0201203909','02/21/17','        ','A',1,'MW1')
,('0201105919','02/21/17','08/24/16','S',2,'MW1')
,('0201105919','02/21/17','08/24/16','S',2,'MW1')
,('0201105919','02/21/17','07/01/15','R',2,'TG1')

query:

select  
  [LN_NUMBER],
  [RUN_DATE]  ,  
  [SALE_DATE],  
  [STATUS_CODE],  
  [TYPE],
  [PROCESSOR_ID],
  ROW_NUMBER () over (
    partition by [LN_NUMBER] 
    order by case when [STATUS_CODE]='A' then 0 else 1 end asc
      , CONVERT(DATE,[SALE_DATE]) desc
  )  as  'RowNum'  
FROM  [dbo].[Closure]  

results:

+------------+----------+-----------+-------------+------+--------------+--------+
| LN_NUMBER  | RUN_DATE | SALE_DATE | STATUS_CODE | TYPE | PROCESSOR_ID | RowNum |
+------------+----------+-----------+-------------+------+--------------+--------+
| 0201105919 | 02/21/17 | 08/24/16  | S           |    2 | MW1          |      1 |
| 0201105919 | 02/21/17 | 08/24/16  | S           |    2 | MW1          |      2 |
| 0201105919 | 02/21/17 | 07/01/15  | R           |    2 | TG1          |      3 |
| 0201203909 | 02/21/17 |           | A           |    1 | MW1          |      1 |
| 0201203909 | 02/21/17 | 09/30/15  | R           |    1 | TG1          |      2 |
| 0201203909 | 02/21/17 |           | R           |    2 | TG1          |      3 |
+------------+----------+-----------+-------------+------+--------------+--------+