mysql - How to order SQL results with year and date? -
i have written postgresql query retrieves month,year , corresponding count. query looks like:
select (case when extract(month app_pgm_req_dt) = 01 'january' when extract(month app_pgm_req_dt) = 02 'february' when extract(month app_pgm_req_dt) = 03 'march' when extract(month app_pgm_req_dt) = 04 'april' when extract(month app_pgm_req_dt) = 05 'may' when extract(month app_pgm_req_dt) = 06 'june' when extract(month app_pgm_req_dt) = 07 'july' when extract(month app_pgm_req_dt) = 08 'augst' when extract(month app_pgm_req_dt) = 09 'september' when extract(month app_pgm_req_dt) = 10 'october' when extract(month app_pgm_req_dt) = 11 'november' when extract(month app_pgm_req_dt) = 12 'december' end) month ,extract(year app_pgm_req_dt) year,count(*) app_pgm_choice group month,year order year as results ordered month , year, ordering month in alphabetical order , want ordered in actual month order.
output:
"september";1968;4 "september";1970;1 "augst";1971;3 "february";1975;1 "february";1977;1 "may";1977;1 "september";1978;1 "february";1978;3 "april";1978;1 "september";1979;1 ..... ..... for year...1978..results should ordered feb, april , sept how can this?
so order month ('01'-'12'):
select case when extract(month app_pgm_req_dt) = 01 'january' when extract(month app_pgm_req_dt) = 02 'february' when extract(month app_pgm_req_dt) = 03 'march' when extract(month app_pgm_req_dt) = 04 'april' when extract(month app_pgm_req_dt) = 05 'may' when extract(month app_pgm_req_dt) = 06 'june' when extract(month app_pgm_req_dt) = 07 'july' when extract(month app_pgm_req_dt) = 08 'augst' when extract(month app_pgm_req_dt) = 09 'september' when extract(month app_pgm_req_dt) = 10 'october' when extract(month app_pgm_req_dt) = 11 'november' when extract(month app_pgm_req_dt) = 12 'december' end month, extract(year app_pgm_req_dt) year, count(*) app_pgm_choice group year, month, to_char (app_pgm_req_dt, 'mm') order year, to_char (app_pgm_req_dt, 'mm');