Reordering Data in TSQL

Question

I have report data that is structured as follows:

RecID | ReportView1 | Name | Value | Total 1 | CURRENT_VIEW | 'Test1' | 10 | 20 1 | PROPOSED_VIEW | 'Test11'| 20 | 30 1 | POTENTIAL_SAVING_VIEW | null | null | 80 2 | CURRENT_VIEW | 'Test1' | 10 | 20 2 | PROPOSED_VIEW | 'Test22'| 25 | 35 2 | POTENTIAL_SAVING_VIEW | null | null | 70 3 | CURRENT_VIEW | 'Test1' | 10 | 20 3 | PROPOSED_VIEW | 'Test33'| 45 | 65 3 | POTENTIAL_SAVING_VIEW | null | null | 90 

I want to reorder based on TOTAL DECS, but only applies to ReportView1 = 'POTENTIAL_SAVING_VIEW'

Thus, the data will be:

 NewRecID | RecID | ReportView1 | Name | Value | Total 1 | 3 | CURRENT_VIEW | 'Test1' | 10 | 20 1 | 3 | PROPOSED_VIEW | 'Test33' | 45 | 65 1 | 3 | POTENTIAL_SAVING_VIEW | null | null | 90 2 | 1 | CURRENT_VIEW | 'Test1' | 10 | 20 2 | 1 | PROPOSED_VIEW | 'Test11' | 20 | 30 2 | 1 | POTENTIAL_SAVING_VIEW | null | null | 80 3 | 2 | CURRENT_VIEW | 'Test1' | 10 | 20 3 | 2 | PROPOSED_VIEW | 'Test22' | 25 | 35 3 | 2 | POTENTIAL_SAVING_VIEW | null | null | 70 

I could probably use a connection with myself, but I'm just wondering, maybe there is an alternative.

thanks

Answer 1

(Edited to account for cases with recurring amounts.)

 ; WITH atable (RecID , ReportView1 , Name , Value , Total) AS ( SELECT 1, 'CURRENT_VIEW ', 'Test1' , 10 , 20 UNION ALL SELECT 1, 'PROPOSED_VIEW ', 'Test11', 20 , 30 UNION ALL SELECT 1, 'POTENTIAL_SAVING_VIEW', null , null , 80 UNION ALL SELECT 2, 'CURRENT_VIEW ', 'Test1' , 10 , 20 UNION ALL SELECT 2, 'PROPOSED_VIEW ', 'Test22', 25 , 35 UNION ALL SELECT 2, 'POTENTIAL_SAVING_VIEW', null , null , 70 UNION ALL SELECT 3, 'CURRENT_VIEW ', 'Test1' , 10 , 20 UNION ALL SELECT 3, 'PROPOSED_VIEW ', 'Test33', 45 , 65 UNION ALL SELECT 3, 'POTENTIAL_SAVING_VIEW', null , null , 90 UNION ALL SELECT 4, 'CURRENT_VIEW ', 'Test1' , 10 , 20 UNION ALL SELECT 4, 'PROPOSED_VIEW ', 'Test11', 20 , 30 UNION ALL SELECT 4, 'POTENTIAL_SAVING_VIEW', null , null , 80 ), ranked AS ( SELECT *, rnk = MAX(CASE ReportView1 WHEN 'POTENTIAL_SAVING_VIEW' THEN Total END) OVER (PARTITION BY RecID) FROM atable ) SELECT NewRecID = DENSE_RANK() OVER (ORDER BY rnk DESC, RecID), RecID, ReportView1, Name, Value, Total FROM ranked ORDER BY rnk DESC, RecID, Total 

Conclusion:

 NewRecID RecID ReportView1 Name Value Total -------- ----- --------------------- ------ ----- ----- 1 3 CURRENT_VIEW Test1 10 20 1 3 PROPOSED_VIEW Test33 45 65 1 3 POTENTIAL_SAVING_VIEW NULL NULL 90 2 1 CURRENT_VIEW Test1 10 20 2 1 PROPOSED_VIEW Test11 20 30 2 1 POTENTIAL_SAVING_VIEW NULL NULL 80 3 4 CURRENT_VIEW Test1 10 20 3 4 PROPOSED_VIEW Test11 20 30 3 4 POTENTIAL_SAVING_VIEW NULL NULL 80 4 2 CURRENT_VIEW Test1 10 20 4 2 PROPOSED_VIEW Test22 25 35 4 2 POTENTIAL_SAVING_VIEW NULL NULL 70 

Answer 2

Try:

 with OrderedTable as ( select recid, total orderedTotal from t1 where reportview1 = 'POTENTIAL_SAVING_VIEW' ) select t1.* from OrderedTable o join t1 on o.recid = t1.recid order by o.orderedTotal desc, t1.total 

Or without CTE:

 select t1.* from ( select recid, total orderedTotal from t1 where reportview1 = 'POTENTIAL_SAVING_VIEW' ) o join t1 on o.recid = t1.recid order by o.orderedTotal desc, t1.total 

Please note that each group of details is ordered by a common ASC, since I assume that you want them to be displayed (based on the example provided)