How to get max id with string in c#

Question

1.00/5 (2 votes)

See more:

Id is:

S001
S1102
S546
S999
S745

i am use this query

"Select max(receipt_no) as rcptno from tb_receipt"

every time return
>>> S999

but correct id is S1102

How to get correct id???

Posted 30-Apr-14 20:24pm

Manamohan Jha

Add a Solution

Comments

RahulMGunjal 1-May-14 4:53am

Its giving you string sorted and not numbers

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Answer 1 · 2014-04-30T20:29:00

Solution 1

SQL does exactly what one should expect. It returns the high value for that type. You think of numeric sorting, but while using string you got string sorting.
You may remove the leading 'S' and let SQL do a numeric sorting...

SQL

SELECT MAX(CAST(REPLACE(RECEIPT_NO, 'S', '') AS INT)) ...

Posted 30-Apr-14 20:29pm

Kornfeld Eliyahu Peter

Comments

DamithSL 1-May-14 3:18am

you assume all the receipt_no start with letter S.

Kornfeld Eliyahu Peter 1-May-14 3:20am

I see what OP shows...The basic idea is to turn it into int, not matter how, than run max on the int...

Abhinav S · Answer 2 · 2014-04-30T20:41:00

Solution 3

You have to keep the string and the number element in the sorting separate.
Sort on the number since you want the larger numbered item.

Posted 30-Apr-14 20:41pm

Abhinav S

Comments

Manamohan Jha 1-May-14 2:45am

How to short correct
Access and sql db

DamithSL · Answer 3 · 2014-04-30T20:36:00

Solution 2

try below

SQL

select top 1 RECEIPT_NO from
tb_receipt order by
CInt(Mid(RECEIPT_NO, 2, len(RECEIPT_NO))) desc

check below answers
http://stackoverflow.com/questions/15969465/picking-the-maximum-value-from-an-alpha-numeric-column-in-ms-access[^]

Posted 30-Apr-14 20:36pm

DamithSL

Updated 30-Apr-14 22:24pm

v6

Comments

Manamohan Jha 1-May-14 2:48am

i think replace is better idea

DamithSL 1-May-14 3:23am

replace is better if you know exactly what you need to replace, if all the elements start with the same letter; we can omit that letter and insert as number. i don't think OP having such database table. I assume there may be some other characters like S given here.

Manamohan Jha 1-May-14 4:09am

substring is not working in access database

DamithSL 1-May-14 4:16am

now only you tell this is access database, next time when you ask question please tag it correctly. You will not get fast and correct answer if not tagged correctly.

Manamohan Jha 1-May-14 4:28am

Okey, Sorry Sir

I want solution for both db(Sql or Access)

DamithSL 1-May-14 4:53am

i have tested on access table and my sql statement return S1102 as result, do you still have issue?

Manamohan Jha 1-May-14 5:10am

No..
Thanks Sir

Kornfeld Eliyahu Peter 1-May-14 2:48am

If you already cast id to int, why not let max do the work. It a faster than select top with order by...

DamithSL 1-May-14 3:26am

if you use max, you won't get the full id value. you only get max number

Manamohan Jha 1-May-14 3:55am

Ya i know that,
max only return max number not with string

Give me any idea to get max value with string???

DamithSL 1-May-14 4:02am

check my updated answer

Manamohan Jha 1-May-14 11:58am

if i use this query "SELECT REPLACE(stu_id, 'S', '') as Id from tb_stu" with connection code they return nothing<br>
<br>
if execute in access database then they return value<br>
<br>
Why???