CODEWITHSUNDEEP
mysqlsql
Average Joe
Average Joe

Reputation: 4601

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

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?

Upvotes: 0

Views: 93

Answers (2)

xbb
xbb

Reputation: 2163

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

Upvotes: 1

nikeaa
nikeaa

Reputation: 1047

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.

Upvotes: 1

Related Questions