How to get highest alphanumeric value in Postgres

Question

I have the following table of inventory stock symbols and want to fetch the highest alphanumeric value which is AR-JS-20. When I say "highest" I mean that the letter order is sorted first and then numbers are factored in, so AR-JS-20 is higher than AL-JS-20.

BTW, I don't want to split anything into parts because it is unknown what symbols vendors will send me in the futire. I simply want an alphanumeric sort like you sort computer directory by name. Where dashes, undersocres, asterisks, etc. come first, then numbers, and letters last with cascading priority where the first character in the symbol has the most weight, then the second character and so on.

NOTE: The question has been edited so some of the answers below no longer apply.

AL-JS-20
AR-JS-20
AR-JS-9
AB-JS-8
AA-JS-1
1A-LM-30
2BA2-1
45HT

So ideally if this table was sorted to my requirements it would look like this

AR-JS-20
AR-JS-9
AB-JS-8
AL-JS-20
AA-JS-1
45HT
2BA2-1
1A-LM-30

However, when I use this query:

select max(symbol) from stock

I get:

AR-JS-9 

but what I want to get is: AR-JS-20

I also tried:

select max(symbol::bytea) from stock 

But this triggers error:

function max(bytea) does not exist

Answer 1

Specify a custom order by that trims everything up to the last - and converts the remaining number to int and take the first:

select stock_code
from mytable
order by regexp_replace(stock_code, '-?[0-9]+-?', ''), regexp_replace(stock_code, '[^0-9-]', '')::int
limit 1

See live demo.

This works for numbers at both start and end of code:

• regexp_replace(stock_code, '-?[0-9]+-?', '') "deletes" digits and any adjacent dashes
• regexp_replace(stock_code, '[^0-9]', '') "deletes" all non-digits

Answer 2

There is dedicated tag for this group of problems: natural-sort (I added it now.)

Ideally, you store the string part and the numeric part in separate columns.
While stuck with your unfortunate symbols ...

If your symbols are as regular as the sample suggests, plain left() and split_part() can do the job:

SELECT symbol
FROM   stock
ORDER  BY left(symbol, 5) DESC NULLS LAST
        , split_part(symbol, '-', 3)::int DESC NULLS LAST
LIMIT  1;

Or, if at least the three dashes are a given:

...
ORDER  BY split_part(symbol, '-', 1) DESC NULLS LAST
        , split_part(symbol, '-', 2) DESC NULLS LAST
        , split_part(symbol, '-', 3)::int DESC NULLS LAST
LIMIT  1

See:

• Split comma separated column data into additional columns

Or, if the format is not as rigid: regular expression functions are more versatile, but also more expensive:

...
ORDER  BY substring(symbol, '^\D+') DESC NULLS LAST
        , substring(symbol, '\d+$')::int DESC NULLS LAST
LIMIT  1;

^ ... anchor to the start of the string
$ ... anchor to the end of the string
\D ... class shorthand for non-digits
\d ... class shorthand for digits

Taking only (trailing) digits, we can safely cast to integer (assuming numbers < 2^31), and sort accordingly.

Add NULLS LAST if any part can be missing, or the column can be NULL.