T-SQL Calculate the max value from an Alpha Numeric key

get max value from alphanumeric string in sql
how to find max value from alphanumeric column in oracle
sql max alphanumeric
sql select max value from multiple columns
sql server select row with max value
sql select row with max value
sql max of two values
sql max in where clause

I have a customers table which has an Alphanumeric key consisting of 5 letters and 3 numbers.

I'm trying to calculate the next 3 digit number in sequence for each 5 letters for example:

Example Key

ALPHA001
ALPHA002
NUMBE001
NUMBE002
NUMBE003
PREST001
PREST002
PREST003
PREST004
PREST005

From the list of keys above i'd like to return the maximum of each unique 5 letter key. i.e.

Returned Values

ALPHA002
NUMBE003
PREST005

You can use row_number():

select top (1) with ties t.*
from table t
order by row_number() over (partition by left(col, patindex('%[0-9]%', col)) order by col desc);

If the letters are fixed then just use left() :

order by row_number() over (partition by left(col, 5) order by col desc);

Find MAX out of Alphanumeric string column - MSDN, Try this function to get only numeric value from non numeric text and use MAX() function to get Max value for nyumeric: CREATE FUNCTION� T-SQL Calculate the max value from an Alpha Numeric key. max for the key type and add 1: MAX(<output from key constraints be temporarily disabled using T-SQL?

First of all: Do not store more than one value within one column. should store the key and the running number in separate columns and combine them just for display purpose...

Try this

DECLARE @mockupTable TABLE(ID INT IDENTITY,YourKey VARCHAR(100));
INSERT INTO @mockupTable VALUES
 ('ALPHA001')
,('ALPHA002')
,('NUMBE001')
,('NUMBE002')
,('NUMBE003')
,('PREST001')
,('PREST002')
,('PREST003')
,('PREST004')
,('PREST005');

WITH cte AS
(
SELECT *
      ,ROW_NUMBER() OVER(PARTITION BY LEFT(YourKey,5) ORDER BY CAST(RIGHT(YourKey,3) AS INT) DESC) AS PartitionedRowNumber 
FROM @mockupTable
)
SELECT *
FROM cte
WHERE PartitionedRowNumber =1;

The result

ID  Key
2   ALPHA002
5   NUMBE003
10  PREST005

Retrieve maximum number from alphanumeric column in Sql , Please can someone help me with SQL query how to get maximum number from an alphanumeric field. Below is the column values. As you� I find your article very helpful. I tried the UNPIVOT function to find the MAX value of multiple columns in my tables. However, I am having a problem filtering the results. It is getting the maximum value, the problem is, that value shows in every row (every record, regardless of record ID).

I'm trying to calculate the next 3 digit number in sequence for each 5 letters

This should do it:

SELECT CONCAT(LEFT(k, 5), FORMAT(MAX(RIGHT(k, 3)) + 1, '000'))
FROM (VALUES
    ('ALPHA001'),
    ('ALPHA002'),
    ('NUMBE001'),
    ('NUMBE002'),
    ('NUMBE003'),
    ('PREST001'),
    ('PREST002'),
    ('PREST003'),
    ('PREST004'),
    ('PREST005')
) tests(k)
GROUP BY LEFT(k, 5)

Cannot Select the max of an alphanumeric column , Cannot Select the max of an alphanumeric column – Learn more on the with enough knowledge to get by the simpler SQL problems. btw, this is specific to SQL Server 2000, but I suspect a solution will DocId is a surrogate key. INSERT INTO #docs (docID, projid, typeid, ver) VALUES (10, 1, 1, 'A'). The value of an approximate numeric value is its significant multiplied by 10 to the exponent. To truly understand the floating point SQL numeric data type, you will have to dig into a little bit of computer science. It can be fun, but at this stage of your SQL journey I believe it is overkill.

You can do this with GROUP BY and MAX:

SELECT  KeyPrefix = LEFT(ExampleKey, 5),
        NextKey = CONCAT(LEFT(ExampleKey, 5), 
                        RIGHT(CONCAT('000', MAX(CONVERT(INT, RIGHT(ExampleKey, 3))) + 1), 3))
FROM    (VALUES
            ('ALPHA001'), ('ALPHA002'), ('NUMBE001'), ('NUMBE002'), ('NUMBE003'), 
            ('PREST001'), ('PREST002'), ('PREST003'), ('PREST004'), ('PREST005')
        ) t (ExampleKey)
GROUP BY LEFT(ExampleKey, 5);

The key operations being:

  1. Get number part of key: RIGHT(ExampleKey, 3)
  2. Convert this to an integer: CONVERT(INT, <output from 1>)
  3. Find the max for the key type and add 1: MAX(<output from 2>) + 1
  4. Pad this with zeros: RIGHT(CONCAT('000', MAX(<output from 3>), 3)
  5. Concatenate withthe original prefix: CONCAT(LEFT(ExampleKey, 5), <output from 4>)

I would however highly recommed storing this in two columns, and use a computed column to combine then:

CREATE TABLE dbo.T
(
    KeyPrefix CHAR(5) NOT NULL,
    KeySequence INT NOT NULL,
    TKey AS CONCAT(KeyPrefix, RIGHT(CONCAT('000', KeySequence), 3))
);

Then your query becomes much simpler:

SELECT  KeyPrefix, 
        KeySequence = MAX(KeySequence) + 1,
        TKey = CONCAT(KeyPrefix, RIGHT(CONCAT('000', MAX(KeySequence) + 1), 3))
FROM    (VALUES
            ('ALPHA', 1), ('ALPHA', 2), ('NUMBE', 1), ('NUMBE', 2), ('NUMBE', 3), 
            ('PREST', 1), ('PREST', 2), ('PREST', 3), ('PREST', 4), ('PREST', 5)
        ) t (KeyPrefix, KeySequence)
GROUP BY KeyPrefix;

Although worth noting that you would never actually need to reconstruct the key as I have done above in the column TKey, you just need the max keysequence.

SQL MIN() and MAX() Functions, Exercise: Use the MIN function to select the record with the smallest value of the Price column. SELECT FROM Products;. Those who’ve stumbled upon this post are probably looking for a way to perform a LIKE statement on a numeric value column in a MySQL, MariaDB or SQL Server Database. This usually happens when we need to restrict our results to those who match the first N starting numbers in that column.

Use this query.

        GO
        ;WITH cte AS
        (
        SELECT ROW_NUMBER() OVER(PARTITION BY LEFT(YourKey,patindex('%[0-9]%', YourKey)) ORDER BY CAST(RIGHT(YourKey,patindex('%[A-Z]%', YourKey)) AS INT) DESC) AS rr , YourKey FROM @mockupTable
        )
        SELECT YourKey FROM cte WHERE rr =1;

        GO

MySQL MAX() Function, Note: See also the MIN() function. Syntax. MAX(expression). Parameter Values. Parameter, Description. expression, Required. A numeric value (� DISTINCT instructs the SUM() function to calculate the sum of the only distinct values. expression is any valid expression that returns an exact or approximate numeric value. Note that aggregate functions or subqueries are not accepted in the expression.

SQL Server MAX() aggregate function, These SQL Server aggregate functions include AVG(), COUNT(), SUM(), MIN() and MAX(). SQL MAX() aggregate function is used to return the maximum value from the The Expression here can be passed to the MAX() aggregate function can be any numeric, ID INT IDENTITY PRIMARY KEY,. "Contact" will always be numeric. From the above picture it worth defining "First/Last Name" as a character and "Contact" as an integer. It is evident that in any application, all fields have one or the other type of data. E.g., numeric, alphabetic, date, and many more. Also, note that different datatype has different memory requirement.

SQL MAX command: MySQL on Maximum or highest value in a field, We will get the record with highest alphabet. But the best way to use MAX command is to apply it in a numeric field. id, name, class, mark. But following on from getting the max value, I have the same issue as Access-SQL Guy: My value columns comes from different underlying tables (in the join statements). So based on the max value that I get back, I need to link back to the underlying table to get additional info from that table where my id and value match. Perhaps more info.

SQLite MAX: Get the Maximum Value in a Set, For example, you can use the MAX function to find the most expensive products, find the biggest item in its group, etc. The following illustrates the basic syntax of�

Comments
  • Use SEQUENCE objects instead of trying to parse and increment strings. Or split such keys into category and ID fields. Such "smart" keys are a big design problem
  • Another option is to create a 'Number Series' structure akin to many ERP systems (See Microsoft Business Central/NAV), so you define the number series' in a separate table and use that to record the 'last used' number in each valid range.
  • I really appreciate this approach with WITH TIES and ORDER BY ROW_NUMBER() - until I compared the execution plans. The problem is, that the sorting is done twice. Quite a draw-back with larger sets...
  • People use it because it looks cool. If you want ties, use RANK.
  • Works a treat thanks. I agree with storing this as two separate values but this is a table from an old legacy system which still requires to be stored as an Alphanumeric.