CODEWITHSUNDEEP
sqlsql-server
Koosh
Koosh

Reputation: 896

trouble select max value from a varchar field

SO I have an interesting issue, I have a varchar field in my table because the numbers have hyphens, so sample data looks like this...

tbla

Field1
102-1
20-1
56-1

What I'm trying to do is somehow select the MAX (highest) value from the Field1,

Desired end result.

Field1
102

the problem is I can't use a query like this...

Select max(field1) from tblA order by fieldA Desc

Because it goes off the first number of FieldA, so if i use the query above it gives me this..

56-1

When In reality I need 102-1

Upvotes: 1

Views: 184

Answers (2)

SqlZim
SqlZim

Reputation: 38023

Depending on what you are doing, you can use top 1 and ordering by the integer part of the field descending, and the entire string descending like so:

select top 1
    Field1
from tbla
order by 
    convert(int,left(Field1,charindex('-',Field1+'-')-1)) desc
  , Field1 desc

rextester demo: http://rextester.com/BCM16018

returns:

+--------+
| Field1 |
+--------+
| 102-1  |
+--------+

In Sql Server 2012 and up: try_convert(datatype,val)will return null when the conversion fails instead of an error.

Upvotes: 1

Konstantin Chernov
Konstantin Chernov

Reputation: 1936

SELECT MAX(CAST(SUBSTRING(field1, 1, CHARINDEX('-',field1)-1) AS int))

Upvotes: 0

Related Questions