CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-bysql-order-by
der_roedie
der_roedie

Reputation: 27

Find max string value in column

I have a table with a column that is used to indicate a position. Positions start with A and could go up to AZ, just like in Excel. E.G.

Row | Position
----|---------
 1  |    A
 1  |    B
 1  |    C
 :  |    :
 1  |    Z
 1  |   AA
 1  |   AB
 2  |    A
 2  |    B

etc.

If I use select max(position) from table where row = 1 the result is always Z and not AB.

How would I select the maximum value of the column which is varchar type?

Upvotes: 0

Views: 1185

Answers (3)

GMB
GMB

Reputation: 222492

Here is one way to do it:

select top 1 position from mytable where row = 1 order by len(position) desc, position desc

This works by sorting records by descending position length, and then by descending position, and then selecting the top record in the resultset.

If you want the per-row maximum, then you can use row_number():

select row, position
from (
    select 
        t.*, 
        row_number() over(partition by row order by len(position) desc, position desc) rn
    from mytable t
) t
where rn = 1

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269973

If you want to do this with MAX() then you can convert the values to right-aligned values:

select max(right('  ' + position, 2))
from t
where row = 1;

(This assumes that the maximum length is 2, based on the reference to Excel.)

You can remove any leading spaces using ltrim():

select ltrim(max(right('  ' + position, 2)))
from t
where row = 1;

The advantage of this approach is that it fits easily into GROUP BY:

select row, ltrim(max(right('  ' + position, 2))) as position
from t
group by row;

Upvotes: 0

forpas
forpas

Reputation: 164099

Left pad the value of position with spaces (if needed), so they are all comparable and then get the max: 

select ltrim(max(right('  ' + position, 2))) 
from tablename
where row = 1

See the demo.
Result (for your sample data):
AB

Upvotes: 3

Related Questions