[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Tricky Sorting
From:       "Shannon Appelcline" <shannon.appelcline () gmail ! com>
Date:       2007-03-31 5:32:08
Message-ID: da3b08430703302232y2603e3d8w46d0c36c5ee50f8e () mail ! gmail ! com
[Download RAW message or body]

I'm trying to figure out the best way to do a tricky bit of sorting.
I'm pretty sure it's entirely possible with an IFNULL or something,
but I always feel like I hit a barrier when I get to a certain level
of complexity in my MYSQL.

In any case, I have some magazines, each of which has a DATE, a
VOLUME, and an ISSUE. The sorting is usually simple, in that the date
includes a year and a month and you can sort by that. However,
sometimes magazines get delayed and they start putting only a year on
their issues, omitting the months. Worse, they sometimes randomly
change numbers (or names)

Ideally, I'd like things to sort by the date, unless there's not a
month, in which case it falls back to the issue number.

So for example, this would be a correct sort:

1996-01-01 Original Mag V1 #1
1996-02-01 Original Mag V1 #2
1996-00-00 Original Mag V1 #3
1996-00-00 Original Mag V1 #4
1996-05-01 Original Mag V1 #5
1996-06-01 Replacement Mag V1 #1

ORDER BY date, volume, issue does this:

1996-00-00 Original Mag V1 #3
1996-00-00 Original Mag V1 #4
1996-01-01 Original Mag V1 #1
1996-02-01 Original Mag V1 #2
1996-05-01 Original Mag V1 #5
1996-06-01 Replacement Mag V1 #1

ORDER BY volume, issue does this:

1996-01-01 Original Mag V1 #1
1996-06-01 Replacement Mag V1 #1
1996-02-01 Original Mag V1 #2
1996-00-00 Original Mag V1 #3
1996-00-00 Original Mag V1 #4
1996-05-01 Original Mag V1 #5

None of it's quite ideal. ORDER BY YEAR(date), volume, issue is what
I'm using right now because it puts things in the right ballpark.

Shannon

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql-marcsub@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]