I think you're right and using an IF is ultimately the right answer. However just substituting issuenum for issuedate doesn't do the right thing.
Looking at my sorting a bit more analytically I think that what I need to do is, WHEN there isn't a month number, THEN substitute the existing month number from the previous issue of the same magazine with the highest month number. So this is more or less the SQL I'm considering: SELECT IF(MONTH(editions.pubdate) > 0, editions.pubdate,???) AS datesort,coreinfo.title,coreinfo.volume,coreinfo.issue FROM coreinfo,editions WHERE coreinfo.category='Magazine' AND coreinfo.title LIKE 'Shadis%' AND coreinfo.mainid=editions.mainid SORT BY datesort,coreinfo.volume,coreinfo.issue The ??? needs to be something like "MAX(editions.pubdate WHERE coreinfo.title IS THE SAME and coreinfo.issue IS LESS THAN THE CURRENT ISSUE)" but I can't quite suss out how to do that particular lookup. Thoughts? Thanks, Shannon -- If I had that On 4/2/07, Brent Baisley <[EMAIL PROTECTED]> wrote:
That should be fairly easy. What you are looking to do is have your sort field conditional. The ORDER BY doesn't have to specify a database field, it can be any field in your query. So you could do something like this: SELECT IF( issuemonth IS NULL, issuenum, issuedate) AS sortfield, issuedate, issuenum,... FROM tablename ORDER BY sortfield. I don't know your field names and table structure, so you'll need to change the names. ----- Original Message ----- From: "Shannon Appelcline" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Saturday, March 31, 2007 1:32 AM Subject: Tricky Sorting > 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] >
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
