I have a database whose columns are npID, title, URL, and issue.
Here is an example of two years' entries:
npID title URL issue
88 EMR Newsletter a.com 2010 Third_Quarter
89 EMR Newsletter b.com 2010 Second_Quarter
43 EMR Newsletter c.com 2010 First_Quarter
47 EMR Newsletter d.com 2009 Winter
45 EMR Newsletter e.com 2009 Summer
46 EMR Newsletter f.com 2009 Spring
44 EMR Newsletter g.com 2009 Fall
What I would like to do is be able to sort the results based on substrings within the "issue" column. However, until 2010, the client used seasons as the header and in 2010, they started using quarters. Is there a way in the "ORDER BY" I can provide a list of words to sort by if/when they're found anywhere in the "issue" value?
I would like the end result to be something like this:
npID title URL issue
43 EMR Newsletter c.com 2010 First_Quarter
89 EMR Newsletter b.com 2010 Second_Quarter
88 EMR Newsletter a.com 2010 Third_Quarter
47 EMR Newsletter d.com 2009 Winter
45 EMR Newsletter e.com 2009 Summer
46 EMR Newsletter f.com 2009 Spring
44 EMR Newsletter g.com 2009 Fall
You can put a CASE
statement in the ORDER BY
to accomplish this. A better route would be to change the application and table to actually store this relevant data in columns where it belongs when you have the development time to do that.
ORDER BY
CAST(SUBSTRING(issue, 1, 4) AS INT) DESC, -- Year
CASE
WHEN issue LIKE '%First_Quarter' OR issue LIKE '%Winter' THEN 1
WHEN issue LIKE '%Second_Quarter' OR issue LIKE '%Spring' THEN 2
WHEN issue LIKE '%Third_Quarter' OR issue LIKE '%Summer' THEN 3
WHEN issue LIKE '%Fourth_Quarter' OR issue LIKE '%Fall' THEN 4
END
Order the seasons however you want. You could also order them in a specific way (Q1 followed by Spring, followed by Q2, etc.) by adjusting the CASE
statement.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With