Query to order start mm/dd end mm/dd chronologically by end month

[goedible]

Ordered by end date so that we can look up today and see what are the most pressing things to do before it’s to late for this year

practice,plant,endM,endD,startM,startD
harvest root,carrot,1,28,6,1
prune,cherries,2,1,1,1
sew seeds,carrot,9,7,3,21

above, the three entries are ordered ascending by endM,endD
Because the first one happens during the new year, it should be at the end of the list.

Can you suggest a modification to attain this goal?
the current query SQL:
SELECT DISTINCT “annual_cultivation_dates_view”.“end_month”, “annual_cultivation_dates_view”.“end_day”, “annual_cultivation_dates_view”.“practice”, “annual_cultivation_dates_view”.“latin_name”, “species”.“Common_name”, “annual_cultivation_dates_view”.“detail”, “plot_plants”.“plot”, “plot_plants”.“sited”, “annual_cultivation_dates_view”.“start_month”, “annual_cultivation_dates_view”.“start_day” FROM “plot_plants”, “Plot”, “annual_cultivation_dates_view”, “species” WHERE “plot_plants”.“plot” = “Plot”.“Plot” AND “annual_cultivation_dates_view”.“latin_name” = “plot_plants”.“latin_name” AND “species”.“Latin_name” = “annual_cultivation_dates_view”.“latin_name” AND “plot_plants”.“sited” > ‘0’ ORDER BY “annual_cultivation_dates_view”.“end_month” ASC, “annual_cultivation_dates_view”.“end_day” ASC, “annual_cultivation_dates_view”.“practice” ASC, “annual_cultivation_dates_view”.“latin_name” ASC, “annual_cultivation_dates_view”.“start_month” ASC

[RobertG]

Would be better to upload a little example, which shows the problem.
Why don’t you set first order to “Year”?

[parsely]

You know that when startM > endM it concerns a row belonging to the next year. You should include that information in the query like.

select .... where ... order by "startM"-"endM", .... rest of order items .... 

or

select ....,"startM"-"endM" as "nextyear" where .... order by "nextyear", ..... rest of order items ...

[goedible]

here is a small example

annual cultivation practices.odb

year is not relevant data

[sokol92]

You can use this exotic construction:

ORDER BY case when  "endM" * 12 + "endD" < "startM" * 12 + "StartD" then "endM" + 12 else "endM" end ASC, "endD" ASC, "startM" ASC, "StartD" ASC

In my opinion, it is better to use the corresponding types in the database for dates.

[goedible]

This logic works for me. I don’t understand why and how to use the corresponding types in the database for dates.
I tried

ORDER BY case when  "endM" * 12 + "endD" < "startM" * 12 + "StartD" then "endM" + 12 else "endM" end ASC, "endD" ASC, "startM" ASC, "StartD" ASC 

and I get

SQL Status: HY000
Error code: 1000

Syntax error in SQL statement /home/buildslave/source/libo-core/dbaccess/source/core/api/SingleSelectQueryComposer.cxx:109

SQL Status: HY000
Error code: 1000

/home/buildslave/source/libo-core/dbaccess/source/core/api/SingleSelectQueryComposer.cxx:108

SQL Status: HY000
Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE /home/buildslave/source/libo-core/dbaccess/source/core/api/SingleSelectQueryComposer.cxx:107

[goedible]

Thank you! smiley: got it to work like this:
SELECT “annual cultivation practices”.“practice” AS “practice”, “annual cultivation practices”.“plant” AS “plant”, “annual cultivation practices”.“endM” AS “endM”, “annual cultivation practices”.“endD” AS “endD”, “annual cultivation practices”.“startM” AS “startM”, “annual cultivation practices”.“StartD” AS “StartD” FROM “annual cultivation practices” “annual cultivation practices” ORDER BY case when “endM” * 12 + “endD” < “startM” * 12 + “StartD” then “endM” + 12 else “endM” end ASC, “endD” ASC, “startM” ASC, “StartD” ASC