problem with select maximum invoice no. with SQL query...

Question

1.00/5 (1 vote)

See more:

When i run this query :-

select max(BillNo) from BillDetails where left(BillNo,6)='13-14/'

It's return only '13-14/9' but I have bill-no this '13-14/10'(which is maximum)..

My Bill No pattern like :-
'13-14/1'
'13-14/2'
'13-14/3'
.....
'13-14/10'
How to get maximum bill no of a same years('13-14/')????
I'm Using MSAccess as a Database..

Thanks in Advanced..

Regards:
Jayanta..

Posted 24-Aug-13 2:38am

JayantaChatterjee

Updated 24-Aug-13 3:18am

v2

Add a Solution

Comments

Mike Meinz 24-Aug-13 8:48am

'13-14/9' is greater than '13-14/10'. A "9" is greater than a "1".

Why do you need maximum invoice number?
Aren't you using an IDENTITY Data Type as the Primary Key of the data table?

With your current pattern, what happens in the year 2100?

2 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)

OriginalGriff · Answer 1 · 2013-08-24T02:55:00

Solution 1

Try:

SQL

SELECT TOP 1 BillNo FROM BillDetails WHERE  BillNo LIKE '13-14/%' ORDER BY CAST(SUBSTRING(BillNo, 7, 999) AS INT)

Posted 24-Aug-13 2:55am

OriginalGriff

Comments

Abhinav S 24-Aug-13 9:03am

Perfect. My 5.

JayantaChatterjee 24-Aug-13 9:14am

Sir,Its doesn't return any billNo..
I also use "SELECT BillNo FROM BillDetails WHERE BillNo LIKE '13-14/%'" , but same result(blank). :-(
Sir, I'm using MSAccess as a Database..

OriginalGriff 24-Aug-13 9:27am

Then you need to look at your data and see what range of values you have in BillNo - it would appear you have no values which start with "13-14/"

JayantaChatterjee 24-Aug-13 9:39am

But Sir, I have bill no from '13-14/1' to '13-14/10' in my "BillDetails" table...

if I have bill No. like 'BI/13-14/000001' to 'BI/13-14/000010' then its return maximum bill no.('BI/13-14/000010'), and query is
"select max(bill_no) from BillDetails where left(bill_no,9)='BI/13-14/'"..
so where I did wrong???

OriginalGriff 24-Aug-13 9:59am

Ah! Then your initial condition was well out - you need to look for a segment of the BillNo.
Try
... LIKE '%13-14/%' ...
and adjust the substring part of the ORDER BY clause to match the numeric portion after the '/' character.

The '%' is a "Match anything" wildcard for LIKE operators , in the same way '*' is for filenames.

JayantaChatterjee 24-Aug-13 10:17am

Sir, I solve that problem with some addition..
I made changed with bill No. before is '13-14/1' now it's '13-14/01' it returns the right value(maximum bill No.)..
thanks you so much for helping me Sir.... :-)

OriginalGriff 24-Aug-13 10:34am

You're welcome!

Abhinav S · Answer 2 · 2013-08-24T03:01:00

Solution 2

You are trying to get the maximum bill number based on parsing a string.
As a result, you wont get the exact value since 1 and 10 are compared differently in string.

You could parse the bill no as above and then convert that number to integer to get the maximum value.

Posted 24-Aug-13 3:01am

Abhinav S

v2