-- find sql env select @@version; --Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
-- dll create table #tmp (sYear int, sNum int); -- dml test data population insert into #tmp values(1950,12); insert into #tmp values(1950,9); insert into #tmp values(1955,11); -- dml solution -- looks like your current state select *, Cast(sYear as char(4)) +''+ Cast(sNum as char(3)) as serialNum from #tmp order by serialNum -- try this select *, Cast(sYear as char(4)) +''+ Cast(sNum as char(3)) as serialNum from #tmp order by sYear, sNum > I have a list of serial numbers that I need listed in order but they > aren't formatted the same, they start with 4 digit year and then item > serial. Ok sounds easy but the serial numbers arent the same length, > so serial #9 for 1950 would be 19509 and serial #101 for 1950 would be > 1950101. So if you sort by the serial and asc 1950101 would come > before 19509 which it shouldnt but mathematically is correct. Having > the client add 0's to serial numbers isnt an option, there aren't very > many like 20 but I would like them to be in order, is there anything > in sql that can do this? > > btw~ using mysql > the relevant sql > > order by serial asc > > this is what some of the current list looks like. > 193932 > 19399 > 1948148 > 194874 > 194878 > 194886 > 1949157 > 1949160 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293930 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
