Taoffi's blog

prisonniers du temps

Sorting dates: Ascending, Descending OR…

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.

Comments are closed