CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Sharanaiyya Swami
Sharanaiyya Swami

Reputation: 127

Select the highest value from mixed string/int column in a stored procedure?

I have a column as varchar. I stored the value in it as follows:-

P.ID
P1
P2
.
.
P6
.
P11

I want to select only the highest id from this column e.g: p11 is my highest id.

My existing query is :

SELECT top(1) P.ID as Result from P order by P.ID desc

Upvotes: 0

Views: 149

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

I think a simple method is:

select top (1) pid
from p
order by len(pid) desc, pid desc;

Upvotes: 1

Sreenu131
Sreenu131

Reputation: 2516

Try This

;WITH CTE ([P.ID])
AS
(
SELECT 'P1' UNION ALL
SELECT 'P2' UNION ALL
SELECT 'P6' UNION ALL
SELECT 'P11' 
)
SELECT TOP 1 *
FROM CTE
ORDER BY  CAST(SUBSTRING([P.ID],PATINDEX('%[0-9]%',[P.ID]),LEN([P.ID])) AS INT) DESC

Result

P.ID
----
P11

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37367

Try this query:

select top 1 *
from P
order by cast(substring(P.ID, 2, len(P.ID)) as int) desc

It just orders by integer number contained in your column.

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

According to what you provide, you can do

SELECT MAX(CAST(RIGHT(YourColumn, LEN(YourColumn)-1) AS BIGINT))
FROM TBL;

eg:

WITH CTE AS
(
  SELECT 'P1' AS Col
  UNION
  SELECT 'P2'
  UNION
  SELECT 'P100'
)
SELECT MAX(CAST(RIGHT(Col, LEN(Col)-1) AS BIGINT))
FROM CTE;

Which will returns: 100

Upvotes: 0

Related Questions