1

I am using MySQL, and I check some SQL tutorial, I see that that is a statement that called ORDER BY, which is usually ORDER BY a column, with AESC or DESC. But is this possible to ORDER the data by different sorting algo?

For example, I have follow value in my DB:

`id`,`name`,`notice_day`

I would like to sort the day with today first, and than the day that near today, and then, tomorrow....bababa, after finish sorting the day from today, and future, yesterday, and so far....Here is the example, assame today is 1/1/2012

`1`, `peter`, `1/1/2012`
`2`, `tom`, `31/12/2011`
`3`, `mary`, `1/2/2012`
`4`, `steve`, `1/1/2011`
`5`, `bill`, `1/4/2012`

The sorting order I would like to is here:

`1`, `peter`, `1/1/2012`

`3`, `mary`, `1/2/2012`

`5`, `bill`, `1/4/2012`

`2`, `tom`, `31/12/2011`

`4`, `steve`, `1/1/2011`
John Woo
  • 258,903
  • 69
  • 498
  • 492
DNB5brims
  • 29,344
  • 50
  • 131
  • 195
  • order by can accept arbitrary functions, e.g. `order by somefield=1` will make all the records with `1` in somefield come out last. – Marc B Aug 04 '12 at 00:43
  • check out http://stackoverflow.com/questions/6186962/sql-query-to-show-nearest-date – Kurt Aug 04 '12 at 07:03

2 Answers2

1

so ascending if notice_day is today or in the future, descending if the notice_day is in the past? could do something like:

select * from my_table order by if(notice_day >= date(now()), notice_day, 9999) asc, notice_day desc

mikeb
  • 366
  • 2
  • 6
0
SELECT * FROM ##### ORDER BY CONVERT(DateTime, EventDate,101)  DESC

mySQL isn't my strong point, but I believe the above should work.

Alexander Wigmore
  • 3,157
  • 4
  • 38
  • 60