I have report data is that structured as followed:
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 apply for ReportView1 = 'POTENTIAL_SAVING_VIEW '
So 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 joining withiin itself BUT I am just wondering it may be there is an alternative.
Thanks
(Edited to account for cases with duplicate totals.)
;
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
Output:
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
Give this a 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
Note each group of Recs is being ordered by total ASC, which is how I guess you want them displayed (based on provided example)
