This is a small story, yet with something special:
Once upon a time, there was a list of events stored into a database table. Each event had (evidenceJ) a date.
A friend of mine asked me to sort these event items.
I asked, as you may imagine: Ascending or Descending?
But this, apparently, seemed too simplistic for him…
- I want the most recent to appear first. He said.
- Well, that means you want to sort in descending order.
I gave him a demo of data sorted in descending order…
- No, he said, that is not what I want!
After a short discussion, I understood that what he wanted was:
· To first have the incoming events (i.e. those located after “today’s” date… sorted in an ascending order)
· Followed by older dates (sorted in ascending order)
I first thought: can ORDER BY do something else than sorting in ascending / descending?...
As the answer was, evidently, No… I said: Sorry, but that is not possible!
The question remained in my mind however. And I started looking for a solution (for myself!)
The experimentations didn’t in fact last long, the solution was quickly found:
All what we need is to:
§ Categorize dates according to their location relative to Today (or any other logic);
§ Give each category a ‘sortable’ value (integer for example)
§ Sort by the category’s value, followed by the date value.
Example:
SELECT TOP (100) PERCENT
event_date,
description,
CASE WHEN event_date >= getutcdate() THEN 1
ELSE 2
END AS date_category -- categorize date
FROM dbo.sight_conferences
ORDER BY date_category, event_date
Screenshot of the output data sample
The key part of this code is the logic of categorizing dates. In this sample, categorization was quite simple (set a category according to date location relative to Today). But, of course, we can use another logic scheme to obtain data sorted accordingly.