sorting - possibly OT

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

sorting - possibly OT

Todd Vainisi-2
Hi List,

I did something kind of foolish and entered dates into my database in integer format, like so: YYYYMMDD.  The date field in this case is sometimes empty, and because there are some holes in my understanding of null values and the date field in general, I decided it would be easiest to check for a 0 rather than try to figure out a way to deal with the wierd date that gets populated in that field if I leave it empty.

In any case, I need to do some sorting when I query this data.  Specifically, I want to be able to say where the rows with date = 0 get placed in the order.

Here's a couple sample rows:

ID  |  DATE

 1      0
 2      20100301
 3      20100302
 4      20100509

What I need to be able to do is to have the data come out earliest date to last date, but with the 0 completely last:

2  20100301
3  20100302
4  20100509
1  0


Am I up the creek?

Todd V



--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/


Reply | Threaded
Open this post in threaded view
|

Re: sorting - possibly OT

Ke Carlton-3
This will do so - however it won't be able to use any indexes in terms
of sorting.

ORDER BY IF(date=0, 99999999,date) ASC

Ke

On 16 March 2010 16:13, Todd Vainisi <[hidden email]> wrote:

> Hi List,
>
> I did something kind of foolish and entered dates into my database in integer format, like so: YYYYMMDD.  The date field in this case is sometimes empty, and because there are some holes in my understanding of null values and the date field in general, I decided it would be easiest to check for a 0 rather than try to figure out a way to deal with the wierd date that gets populated in that field if I leave it empty.
>
> In any case, I need to do some sorting when I query this data.  Specifically, I want to be able to say where the rows with date = 0 get placed in the order.
>
> Here's a couple sample rows:
>
> ID  |  DATE
>
>  1      0
>  2      20100301
>  3      20100302
>  4      20100509
>
> What I need to be able to do is to have the data come out earliest date to last date, but with the 0 completely last:
>
> 2  20100301
> 3  20100302
> 4  20100509
> 1  0
>
>
> Am I up the creek?
>
> Todd V
>
>
>
> --
> This list is a free service of LassoSoft: http://www.LassoSoft.com/
> Search the list archives: http://www.ListSearch.com/Lasso/Browse/
> Manage your subscription: http://www.ListSearch.com/Lasso/
>
>
>

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/


Reply | Threaded
Open this post in threaded view
|

Re: sorting - possibly OT

Todd Vainisi-2
In reply to this post by Todd Vainisi-2
Super!  Thank you!

**********************

This will do so - however it won't be able to use any indexes in terms
of sorting.

ORDER BY IF(date=0, 99999999,date) ASC

Ke

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/