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/[EMAIL PROTECTED]
