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.

Community · Accepted Answer · 2017-05-23 10:32:19Z

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, 2017 at 10:32

CommunityBot

11 silver badge

answered Dec 21, 2013 at 5:47

Upendra Chaudhari

6,5515 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vishal Suthar Over a year ago

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

Upendra Chaudhari Over a year ago

yes, I know, it was just for example, still I have updated my answer.

Vishal Suthar · Accepted Answer · 2013-12-21 06:02:29Z

0

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

answered Dec 21, 2013 at 6:02

Vishal Suthar

17.2k4 gold badges64 silver badges109 bronze badges

Comments

MQuirion · Accepted Answer · 2013-12-21 05:42:01Z

0

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

answered Dec 21, 2013 at 5:42

MQuirion

3232 silver badges6 bronze badges

Comments

Vignesh Kumar A · Accepted Answer · 2013-12-21 05:55:44Z

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, 2013 at 5:55

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

Collectives™ on Stack Overflow

Data Ordering & Select Max SQL Server

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related