Re: Order by a number not leading in zero
Scott Haneda wrote: I have the following... tSql = SELECT l.team, l.link, lc.category, l.age, l.date FROM league AS l INNER JOIN league_category AS lc ON l.category = lc.id WHERE lc.id = prepSQL(tConn, id) AND l.status = '1' ORDER BY l.age, l.date; L.age has data in it as varchar() where there are some without a leading zero. Bit of a late response ... try: ORDER BY lpad( l.age, 7, '0' ), l.date It might slow down response as index on I.age is no longer used by sort. On the long term consider storing the value with prepended zeros and trim them during select: SELECT l.team, l.link, lc.category, trim(leading '0' from l.age ), l.date HansH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order by a number not leading in zero
I have the following... tSql = SELECT l.team, l.link, lc.category, l.age, l.date FROM league AS l INNER JOIN league_category AS lc ON l.category = lc.id WHERE lc.id = prepSQL(tConn, id) AND l.status = '1' ORDER BY l.age, l.date; L.age has data in it as varchar() where there are some without a leading zero. Rsults end up like 10U 11U 12U 5U 6U 7U I would like to order those as 5U 6U 7U 10U 11U 12U Thanks for the help. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by a number not leading in zero
Hi Scott, This kind of ordering thing has come up before and there's never a great answer I don't think. You could try this ORDER BY ORDER BY l.age + 0.0, l.age, l.date But I think that might cause sorting problems if age has leading 0s. Maybe not... Matt - Original Message - From: Scott Haneda Sent: Thursday, November 13, 2003 5:34 PM Subject: Order by a number not leading in zero I have the following... tSql = SELECT l.team, l.link, lc.category, l.age, l.date FROM league AS l INNER JOIN league_category AS lc ON l.category = lc.id WHERE lc.id = prepSQL(tConn, id) AND l.status = '1' ORDER BY l.age, l.date; L.age has data in it as varchar() where there are some without a leading zero. Rsults end up like 10U 11U 12U 5U 6U 7U I would like to order those as 5U 6U 7U 10U 11U 12U Thanks for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
