I have table with a column Order of type varchar as follows
Order
-----
Ord-998,
Ord-999,
Ord-1000,
Ord-1001,
I want to get the max value as 1001
But when I run this query, I am getting 999 as max value always
select
SUBSTRING((select isnull(MAX(OrderNo), '0000000')
from OrderSummary
where OrderNo like 'Ord%'), 5, 10) as [OrderMax]
Can anybody provide a solution?
Since you are maxing a string it is sorting alphabetically where C is larger than AAA and 9 is larger than 10. Remove the letters and cast it as an int then get the max. Given that it will always be Ord-### we can remove the Ord- and cast the remainder as an INT.
SELECT
MAX(CAST(SUBSTRING(OrderNo,5,LEN(OrderNo)-4) AS INT))
FROM OrderSummary
WHERE OrderNo LIKE 'Ord-%'
Another possibility would be the REPLACE-function:
SELECT
MAX(CONVERT(int,(REPLACE(OrderNo, 'Ord-' ,'')))) AS OrderMax
FROM OrderSummary;
But like already mentioned in comments, the best solution will be to get rid of the "Ord-" and create a int-column instead.
If I were tackling this issue I would Order Number as an integer instead of a varchar, and not store the repetitive text "Ord-" in the field. If end users require "Ord-998", I'd handle it via my CRUD layer.
This also allows you to declare the Order Number column as an identity column, which will handle the automatic incrementing for you.
Please try this query in SQL Server
select MAX(convert(int, substring(orderno, 5, LEN(orderno))))
from OrderSummary
I attach another possible solution this query is posgresQL
SELECT
CONCAT(RPAD('Ord-',5), MAX(CAST(SUBSTRING(OrderNo,5,length(OrderNo)-4) AS INT)))AS
ORDER
FROM OrderSummary
RESULT
Order Ord-1001
Use len keyword instead of like keyword
select MAX(CAST(SUBSTRING(OrderNo, 6, len(OrderNo)) AS INT)) FROM OrderSummary
The problem, as stated in other answers is that you have a string. I will provide here an alternative solution: you could select top 1 from your table and have an order by len(OrderNo) desc, OrderNo desc. This way you will not have to cut all the varchars and convert them to integer, but will be able to simply compare their length (very quick) and if similar, their varchar value (pretty slow). Of course, a default for null values is a good idea, but make sure you do not put more 0-s there than the number of digits of your first number.
u can cast as int,it will consider value,not lexic. order
ie.
max(cast(Col as int)) from Table
