[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql query to get max userid in db

Posted on 2011-05-04
11
Medium Priority
?
378 Views
Last Modified: 2012-05-11
I generate a new userid in my sql query by using some function
now lets says my created

 userid is @userid="jmdoe"
Now i want  to write a query to check in  a table tbluserid
the max jmdoe that exist
lets say ther is a jmdoe20 in table then
@maxuserid="jmdoe20"


lets say userid is @userid="gmtoe"
 and the max userid with id is gmtoe2
then
@maxuserid="gmtoe2"
0
Comment
Question by:Angela4eva
11 Comments
 
LVL 6

Expert Comment

by:hyphenpipe
ID: 35690271
select max(userid) from tbluserid
 where userid like 'jmdoe%'
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35690278
Is this what you mean?


select @maxuserid = top 1 userid
from myTable
where userid like 'jmdoe%'
order by userid desc
0
 
LVL 16

Expert Comment

by:Imran Javed Zia
ID: 35690293
Please use following:

Select max(userid) from tbluserid
Where userid like 'gmtoe%'


to get data in @maxuserid

Select @maxuserid=max(userid) from tbluserid
Where userid like 'gmtoe%'
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:Angela4eva
ID: 35690444
what happens if there uis a userid called jmdoes25?
it should ofcourse ignore this user. None of the answers above deal with this kind of scenario.
0
 
LVL 16

Expert Comment

by:Imran Javed Zia
ID: 35690487
if you pass jmdoes25 and it is the max then it will be returned itself
0
 

Author Comment

by:Angela4eva
ID: 35690512
IJZ,
then that will be incorrect...please see the s before 25.
0
 

Author Comment

by:Angela4eva
ID: 35690564
Here is what i think should be algorithm

get the string that matches jmdoe and get the subsctring after jmdoe it is a number then get the mac one if not number ignore.
Any help?
0
 
LVL 16

Expert Comment

by:Imran Javed Zia
ID: 35690614
you can make consider the following:

just use specific characters to get the related max 3 char, 4 char or etc as per your suitability
remove the numeric part from string and then us it

0
 

Author Comment

by:Angela4eva
ID: 35690645
IJZ,
not a vaiable solution
0
 
LVL 9

Accepted Solution

by:
anillucky31 earned 1000 total points
ID: 35690719
This is generic solution for your problem

select MAX(userid) from tbluserid where substring(userid, 1, LEN(@userid)) = @userid
and ISNUMERIC(substring(userid, LEN(@userid) + 1, LEN(@userid)  )) = 1

0
 

Author Comment

by:Angela4eva
ID: 35691266

almost
select MAX(userid) from tbluserid where substring(userid, 1, LEN(@userid)) = @userid
and( (ISNUMERIC(substring(userid, LEN(@userid) + 1, LEN(@userid)  )) = 1) or (len(userid)=len(@userid)))
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question