sql query help in sorting a field that's got both string and numbers

Question

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.34452

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?

nikeaa · Accepted Answer · 2012-07-31 22:19:55Z

1

First of all, unless all of your RECIDs are exactly five characters long forever and always, the select you put up won't work, becuase "shop.order.9" would come out as larger than "shop.order.10", which is wrong.

What I think we need to do here is extract the numeric part, cast it to an integer, and sort by that. Now, I don't have access to mySQL, so this may not be exactly right, but it should be close ...

SELECT
    CAST(SUBSTRING_INDEX(RECID,'.',-1) AS INT) AS RecIdNumber
FROM
    table
WHERE
    RECID LIKE 'shop.order.%'
ORDER BY
    RecIdNumber DESC
LIMIT
    0, 1

This will take the part after the last dot, convert it to an INT, name it 'RecIdNumber', and sort by that.

I hope this helps.

edited Jul 31, 2012 at 22:19

answered Jul 31, 2012 at 22:14

nikeaa

1,0477 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim Lehner Over a year ago

select max(cast(substring_index(source_recid,'.',-1) as unsigned)) from table;

Average Joe Over a year ago

Thanks to all. But the only one worked was Tim's. I was getting an error around integer or it was not giving me the number. for some reason, Tim's worked., So, Tim, please submit your comment as answer so I chose it.

xbb · Accepted Answer · 2012-07-31 22:26:19Z

1

SELECT CAST(SUBSTRING_INDEX(field,'.',-1) AS INT) AS RID
FROM yourtable
WHERE
RECID LIKE 'shop.order.%'
ORDER BY
RID DESC

answered Jul 31, 2012 at 22:26

xbb

2,1731 gold badge21 silver badges37 bronze badges

Collectives™ on Stack Overflow

sql query help in sorting a field that's got both string and numbers

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related