Order By clause with multiple columns using Case statement-CodePudding

In the query below I need the sortest_tesc_code sort order to be hard-coded as shown in the order by case statement. This works, but I also need a second sort so that multiple sortest_tesc_code values will be sorted by their sortest_test_date in descending order. How do I add the second sort?

SELECT  stvtesc_desc testname,
    nvl(sortest_test_score, '-') testscore,
    nvl(sortest_tesc_code, ' ') placement,
    nvl(SORTEST_TSRC_CODE, ' ') method_cocc,
    nvl(TO_CHAR(sortest_test_date,'DD-MON-YYYY'),' - ') testdate
FROM stvtesc, sortest_add
WHERE (stvtesc_vr_msg_no <> '999' or stvtesc_vr_msg_no is null)
AND   sortest_tesc_code not in ('CASM','CASR','CASW')
AND   sortest_tesc_code = stvtesc_code
AND   sortest_pidm      = testpidm             
ORDER BY
      case 
         when sortest_tesc_code = 'PLM' then 1 
         when sortest_tesc_code = 'PLW' then 2
         when sortest_tesc_code = 'MEX' then 3
         when sortest_tesc_code = 'WEX' then 4
         when sortest_tesc_code = 'BWC' then 5
         when sortest_tesc_code = 'BWX' then 6
         when sortest_tesc_code = 'OOC' then 7
         when sortest_tesc_code = 'HSRT' then 8
         when sortest_tesc_code like 'TE%' then 9
         when sortest_tesc_code = 'COMP' then 10
      end;

Query result with the sortest_test_date not sorted as desired for sortest_tesc_code PLM:

Placement Level Math    015 PLM DSP 14-APR-2018
Placement Level Math    018 PLM ALKS    20-MAY-2020
Placement Level Math    010 PLM DSP 15-MAY-2019
Placement Level Writing 009 PLW DSP 20-MAY-2019

CodePudding user response：

That's because you didn't instruct Oracle to do it. You're sorting only by sortest_tesc_code & are missing sortest_test_date, i.e.

ORDER BY
  case 
     when sortest_tesc_code = 'PLM' then 1 
     when sortest_tesc_code = 'PLW' then 2
     when sortest_tesc_code = 'MEX' then 3
     when sortest_tesc_code = 'WEX' then 4
     when sortest_tesc_code = 'BWC' then 5
     when sortest_tesc_code = 'BWX' then 6
     when sortest_tesc_code = 'OOC' then 7
     when sortest_tesc_code = 'HSRT' then 8
     when sortest_tesc_code like 'TE%' then 9
     when sortest_tesc_code = 'COMP' then 10
  end,
  sortest_test_date                            --> this
;