Might need some type coercion: SELECT SUBSTRING(recid, 13, <column size>) AS numbers FROM table ORDER BY SUBSTRING(recid, 13, <column size>)+0 DESC
> -----Original Message----- > From: Paul Halliday [mailto:paul.halli...@gmail.com] > Sent: Tuesday, July 31, 2012 3:27 PM > To: Haluk Karamete > Cc: MySQL > Subject: Re: query help > > On Tue, Jul 31, 2012 at 7:02 PM, Haluk Karamete > <halukkaram...@gmail.com> wrote: > > I've got a text field called source_recid. It stores half string half > > number like strings in it. > > > > Example > > > > shop.orders.32442 > > > > the syntax is DATABASENAME.TABLENAME.RECID > > > > My goal is to scan this col and find out the biggest RECID ( the > > integer) in it. > > > > So, in a case like this > > > > shop.orders.32442 > > shop.orders.82000 > > shop.orders.34442 > > > > It would be the record whose source_recid is shop.orders.82000. Why? > > Cause > > 82000 happens to be the largest integer. > > > > What SQL statement would get me that record? > > > > One option to this is to create a new column ( the_ids ) and move all > > the integers in it and then run something like this > > > > select source_recid from mytable where source_recid like > 'shop.orders.%' > > order by the_ids DESC LIMIT 1 > > > > Is there a way to pull this off without going thru this step? > > Would substring work? > > SELECT SUBSTRING(recid, 13, <column size>) AS numbers FROM table ORDER > BY numbers DESC > > > -- > Paul Halliday > http://www.pintumbler.org/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
