Hello!
I have got a tabale with dates:
2006-12-01
2007-01-12
2006-12-31
2008-01-05
2006-12-08


I want to display ALL dates, but sort this way, that the closest dates
are on top, next there are future dates, and at the end are past dates:
2007-01-12
2008-01-05
2006-12-31
2006-12-08
2006-12-01

How do this? Should I create temporary table and load dates with my
conditons or do something else?
Does solution with temporary table is efficient?

krzys wrote:

surely "closest" dates are also future dates??

And is today a closest date, a past date or a future date?

Assuming that the date field is called date_field and the table is called
date_table, try:

SELECT date_field FROM date_table WHERE date_field > NOW() ORDER BY
date_field
UNION
SELECT date_field FROM date_table WHERE date_field <= NOW() ORDER BY
date_field DESC

you may need to play around with the where clause depending on the answer to
one of above questions.

Paul Lautman wrote:


That's what I tried and got:

ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY


--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png

krzys wrote:


First, it would be interesting to note the business case for needing data
presented in this manner - in generic terms of course... no need for details...

ORDER BY - which uses a simple sort-routine can only do ascending or decending -
not mix-cending . If there are secondary keys, you could get closer,
depending on that key value - such as another date. Even that will still only
do ascending/descending.

If these dates could in some way be transformed into Spatial data, then you
would have a way to find "points" that are further and further away from the
starting point. This is used on some sites to find "stores" near your current
zip code and can order them based on distance.

The link below would appear to be an excellent starting point - you will need to
think WAY outside the box. Don't forget - in your spatial data table, you would
need a column that is the "date" that can be joined to the other table.

http://dev.mysql.com/tech-resources/...ith-mysql.html

This may help you get started...
mysql> describe mm;
+-------+-----------+------+-----+-------------------+-------+
3 rows in set (3.93 sec)

mysql> insert into mm values (current_timestamp,current_date,current_time);
Query OK, 1 row affected (0.21 sec)

mysql> select * from mm;
+---------------------+------------+----------+
1 row in set (0.01 sec)

mysql> select UNIX_TIMESTAMP(a), UNIX_TIMESTAMP(b), UNIX_TIMESTAMP(FROM_UNIXTIME
(c)) from mm;
+-------------------+-------------------+----------------------------------+
1 row in set (0.02 sec)

If stored "spatially", then you can calculate "distance" between two dates and
sort by that "distance" - but of course, you would then only display the dates.

--
Michael Austin.
Database Consultant

Michael Austin wrote:
Interesting, but isn't the UNION approach rather easier?

Paul Lautman wrote:


That will not get what he wants... He said he want dates that are "closest" on
top. With "closest" in the picture, you cannot use the UNION method you described.

given today is 6-Jan-2007:

1-dec-2006
31-dec-2006
5-Jan-2007
7-jan-2007
1-dec-2007

Which is closer? 5-Jan-2007 or 7-Jan-2007. is there an actual full timestamp or
just a date? The answer is why you want the data returned in this method and
can future dates be cloesest dates - if no - then the union method also does not
work per se, due the fact that you cannot order by in a union.... But you can
create views...

mysql> create view cl1 as select * from mm where b = (select max(b) from mm wher
e b < NOW()) order by b;
Query OK, 0 rows affected (3.72 sec)

mysql> create view cl2 as select * from mm where b > NOW() order by b;
Query OK, 0 rows affected (3.44 sec)

mysql> create view cl3 as select * from mm where b < (select max(b) from mm wher
e b < NOW()) order by b; // we already have the MAX(b) less than NOW().
Query OK, 0 rows affected (3.43 sec)

mysql> create view c4 as select * from cl1 union select * from cl2 union select
* from cl3;
Query OK, 0 rows affected (3.67 sec)

mysql> select * from c4
-> ;
+---------------------+------------+------+
6 rows in set (0.22 sec)


If closest is future OR past, then the Spatial route is the only way that will
work 100% of the time. Again, goes to the business case for wanting data
returned in this method in the first place...

--
Michael Austin.
Database Consultant