From: Rick James
Date: August 1 2012 5:03pm
Subject: RE: query help
List-Archive: http://lists.mysql.com/mysql/227923
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB1489DB9CEE@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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.halliday@stripped]
> Sent: Tuesday, July 31, 2012 3:27 PM
> To: Haluk Karamete
> Cc: MySQL
> Subject: Re: query help
>=20
> On Tue, Jul 31, 2012 at 7:02 PM, Haluk Karamete
> <halukkaramete@stripped> 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?
>=20
> Would substring work?
>=20
> SELECT SUBSTRING(recid, 13, <column size>) AS numbers FROM table ORDER
> BY numbers DESC
>=20
>=20
> --
> Paul Halliday
> http://www.pintumbler.org/
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql