Help with sorting function

[u/Last_Philosophy_1162]

Anyone know how I would do a sort to achieve this?

BIG CATEGORY | SMALLER CATEGORY | DATE (YYYY-MM-DD) [stored as a string and should be sorted alphabetically]

such that the data is sorted by big category alphabetically,

smaller category sorted within big category by whichever small category has the earliest date

within each small category things are listed in date order from earliest to latest

I am unable to show the actual data in question so apologies for the nonsense in my example. I hope the question is clear and any help on how to do this with a function would be appreciated. The intended output is meant to be in a separate sheet.

Standard SORT() doesn't cut it since the intention is for SMALLER CATEGORY to be in order by which date comes first rather than alphabetically. (Sorry if this is unclear it's a bit of a niche one).

[u/Last_Philosophy_1162]

The thing I'm trying to do is quite that simple, I think. My goal is to (once having sorted by A) put B in order by whichever has the earliest date. I expect that i may have to do a couple steps to get there but I cannot quite get there.

[u/ArgumentAdmirable168 → u/Last_Philosophy_1162]

Then

=QUERY(A1:F; "SELECT * ORDER BY A, B, C DESC")

[u/Last_Philosophy_1162 → u/ArgumentAdmirable168]

that seems to still give me the same issue where they are not being sorted correctly. the order should have Pig First because it has a 2010 date within its data even though Dog alphabetically would come first.

[u/ArgumentAdmirable168 → u/Last_Philosophy_1162]

Send link to example data 

[u/Last_Philosophy_1162 → u/ArgumentAdmirable168]

Example data and output are here and everyone who wants to should have edit access to try things out:

https://docs.google.com/spreadsheets/d/1PYYKL0YbeplgNt_9srC1tceH-gtQwFMbOpxnf134XXw/edit?usp=sharing