How can I modify this query so that the latin_names stay together (grouped) and the groups of latin_name are descending by end_month, end_day. For example instead of Acer macrophylum 12-21 being the first group, it would be Allium porrum 8-28 because endmonth,enday 8-23 is closer, more urgent.
SELECT DISTINCT “annual_cultivation_dates_view”., “daterange”., “species”.“Common_name”, “plot_plants”.“plot”, “Plot”.* FROM “annual_cultivation_dates_view”, “daterange”, “species”, “plot_plants”, “Plot” WHERE “plot_plants”.“plot” = “Plot”.“Plot” AND “plot_plants”.“latin_name” = “species”.“Latin_name” AND “species”.“Latin_name” = “annual_cultivation_dates_view”.“latin_name” AND CAST( YEAR( CURRENT_DATE ) || ‘-’ || RIGHT( ‘0’ || “startmonth”, 2 ) || ‘-’ || RIGHT( ‘0’ || “startday”, 2 ) AS DATE ) BETWEEN CAST( YEAR( CURRENT_DATE ) || ‘-’ || RIGHT( ‘0’ || “start_month”, 2 ) || ‘-’ || RIGHT( ‘0’ || “start_day”, 2 ) AS DATE ) AND CAST( CASE WHEN “start_month” <= “end_month” THEN YEAR( CURRENT_DATE ) ELSE YEAR( CURRENT_DATE ) + 1 END || ‘-’ || RIGHT( ‘0’ || “end_month”, 2 ) || ‘-’ || RIGHT( ‘0’ || “end_day”, 2 ) AS DATE ) AND CAST( YEAR( CURRENT_DATE ) || ‘-’ || RIGHT( ‘0’ || “endmonth”, 2 ) || ‘-’ || RIGHT( ‘0’ || “endday”, 2 ) AS DATE ) BETWEEN CAST( YEAR( CURRENT_DATE ) || ‘-’ || RIGHT( ‘0’ || “start_month”, 2 ) || ‘-’ || RIGHT( ‘0’ || “start_day”, 2 ) AS DATE ) AND CAST( CASE WHEN “start_month” <= “end_month” THEN YEAR( CURRENT_DATE ) ELSE YEAR( CURRENT_DATE ) + 1 END || ‘-’ || RIGHT( ‘0’ || “end_month”, 2 ) || ‘-’ || RIGHT( ‘0’ || “end_day”, 2 ) AS DATE ) ORDER BY “latin_name” ASC, CASE WHEN “end_month” * 12 + “end_day” < “start_month” * 12 + “start_day” THEN “end_month” + 12 ELSE “end_month” END ASC, “end_day” ASC, “start_month” ASC, “start_day” ASC
