Data Ordering & Select Max SQL Server

Question

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.

score 1 · Answer 1 · edited May 23 '17 at 10:32

1

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

edited May 23 '17 at 10:32

Community

1
1

answered Dec 21 '13 at 05:47

Upendra Chaudhari

6,473
5
25
42

`user` is a reserved word in sql so it should be used by brackets around that `[user]` – Vishal Suthar Dec 21 '13 at 06:07
yes, I know, it was just for example, still I have updated my answer. – Upendra Chaudhari Dec 21 '13 at 06:09

score 0 · Answer 2 · answered Dec 21 '13 at 05:42

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` ;

score 0 · Answer 3 · answered Dec 21 '13 at 05:55

0

Try this query

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

OP

US10 | John

answered Dec 21 '13 at 05:55

Vignesh Kumar A

27,863
13
63
115

score 0 · Accepted Answer · answered Dec 21 '13 at 06:02

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

Data Ordering & Select Max SQL Server

4 Answers4