From: Paul Halliday
Date: July 31 2012 10:26pm
Subject: Re: query help
List-Archive: http://lists.mysql.com/mysql/227918
Message-Id: <CAJfn-RFydmb10aV+0wikJW8sbN+zOn_iffAy5KbeZ_EDAy0EyQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1

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?

Would substring work?

SELECT SUBSTRING(recid, 13, <column size>) AS numbers FROM table ORDER
BY numbers DESC


-- 
Paul Halliday
http://www.pintumbler.org/