CODEWITHSUNDEEP
sqlsql-server
Willy Lazuardi
Willy Lazuardi

Reputation: 1816

Data Ordering & Select Max SQL Server

Let say I have a user table and the record is like this:

----------------------
| User ID | Username |
----------------------
|US1      | Andy     |
|US2      | Boston   |
|US3      | Charlie  |
|US4      | Donnie   |
|US5      | Elmo     |
|US6      | Frank    |
|US7      | Garry    |
|US8      | Henry    |
|US9      | Ignatius |
|US10     | John     |

What I need is 

US10      | John

Then I do:

SELECT MAX (UserId) FROM User;

The result was

US9      | Ignatius

because its ordered by string literally, so US10 is not the max

What should I do to accomplished that result? Thanks in advance.

Upvotes: 0

Views: 74

Answers (4)

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

If you have fixed format for data like numbers are at last in string then you can use PATINDEX() function to get number from string like 

SELECT TOP 1 * FROM [User]
ORDER BY CAST(SUBSTRING(UserId, PATINDEX('%[0-9]%', UserId), LEN(UserId)) AS INT) DESC;

If you have not fixed format for number in string and you want to sort data based on numbers only then check more details in below link

SQL Server 2008 - order by strings with number numerically

Upvotes: 1

Vishal Suthar
Vishal Suthar

Reputation: 17194

NOTE: keep in mind that user is a reserved word in sql so it should be used by brackets around that [user]

There are many options:

1) 

select top 1 Userid,
CAST(SUBSTRING(UserId, PATINDEX('%[0-9]%', UserId), LEN(UserId)) AS INT) as Maxid,
username
from [user] 
Order By Maxid desc

Output:

Userid  Maxid   username
=============================
US10    10      John

2) 

SELECT TOP 1 * FROM [User]
ORDER BY CAST(RIGHT(UserId,LEN(UserId)-2) AS INT) DESC

Output

userid  username
=====================
US10    John

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

Try this query

SELECT TOP 1 * FROM User ORDER BY CAST(RIGHT(UserId,LEN(UserId)-2) AS INT) DESC

OP

US10 | John

Upvotes: 0

MQuirion
MQuirion

Reputation: 323

Assuming that your column format always have the same quantity of character at the beginning, you can use this query to remove the first 2 characters and convert the remaining to number in order the get the proper behaviour on your max query : 

SELECT MAX(CAST(SUBSTRING(`UserId` FROM 3) AS UNSIGNED)) FROM `User` ;

Upvotes: 0

Related Questions