MAX function selecting just 10 values but values are more than 10. help me with this

Question

0.00/5 (No votes)

See more:

DECLARE @Installment_no VARCHAR(100) SELECT @Installment_no = Max(v.Installment_no) FROM Voucher_slip v,Loan l,Installments i,Customer c WHERE l.Loan_id=v.Loan_id and c.Loan_id=v.Loan_id and c.Customer_CNIC = '12345-1234567-8'; 

Select ins.Transaction_no  from Voucher_slip v,Loan l,Installments i,Customer c,Installment_Payment ins where l.Loan_id=v.Loan_id and c.Loan_id=v.Loan_id and l.Loan_id=ins.Loan_id and c.Customer_CNIC = '12345-1234567-8' and ins.Transaction_no=@Installment_no

Posted 21-Feb-13 21:09pm

zeshanazam

Add a Solution

Comments

[no name] 22-Feb-13 3:23am

Please specify your problem more elaborately...

zeshanazam 22-Feb-13 4:36am

i m getting installment no 10, but maximum installment no is 12

[no name] 22-Feb-13 4:58am

Out of 12 instalments your condition might be satisfying only 10 thats why you are getting 10. Please check at your DB.

zeshanazam 22-Feb-13 5:06am

i have checked everything carefully, but found nothing...

[no name] 22-Feb-13 5:16am

Show me the results of the following queries separately.
1. Select ins.Transaction_no from Voucher_slip v,Loan l,Installments i,Customer c,Installment_Payment ins where l.Loan_id=v.Loan_id and c.Loan_id=v.Loan_id and l.Loan_id=ins.Loan_id and c.Customer_CNIC = '12345-1234567-8';

2. Select ins.Transaction_no from Voucher_slip v,Loan l,Installments i,Customer c,Installment_Payment ins where l.Loan_id=v.Loan_id and c.Loan_id=v.Loan_id and l.Loan_id=ins.Loan_id and c.Customer_CNIC = '12345-1234567-8' and ins.Transaction_no=@Installment_no

zeshanazam 22-Feb-13 5:44am

Result of first query

1
2
3
4
5
6
7
8
9
10

zeshanazam 22-Feb-13 5:46am

result of 2nd query:
9

[no name] 22-Feb-13 5:53am

Now I can say your 1st query in the qn returns the count of the max records as 9.
But in your 2nd query you are getting 10 records satisfying the condition so its returning the 10 records. rest of the records are not matched with the conditions.

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)

Tejas Vaishnav · Answer 1 · 2013-02-21T23:26:00

Hello friend,

If you want to select TOP 10 values from your table then use something like this...

SQL

Select TOP 10 ins.Transaction_no  from Voucher_slip v,
Loan l,
Installments i,
Customer c,
Installment_Payment ins 
where l.Loan_id=v.Loan_id 
and c.Loan_id=v.Loan_id 
and l.Loan_id=ins.Loan_id 
and c.Customer_CNIC = '12345-1234567-8' 
ORDER BY v.Installment_no DESC

If you want to select only one value from your table then use something like this...

SQL

Select TOP 1 ins.Transaction_no  from Voucher_slip v,
Loan l,
Installments i,
Customer c,
Installment_Payment ins
where l.Loan_id=v.Loan_id
and c.Loan_id=v.Loan_id
and l.Loan_id=ins.Loan_id
and c.Customer_CNIC = '12345-1234567-8'
ORDER BY v.Installment_no DESC

gvprabu · Answer 2 · 2013-02-22T00:17:00

Hi Try this,

SQL

SELECT Transaction_no 
FROM Installment_Payment
WHERE Transaction_no IN( SELECT TOP 1 ins.Transaction_no  
				     FROM Voucher_slip v, Loan l, Installments i, Customer c, Installment_Payment ins
				     WHERE l.Loan_id=v.Loan_id
				     AND c.Loan_id=v.Loan_id
				     AND l.Loan_id=ins.Loan_id
				     AND c.Customer_CNIC = '12345-1234567-8'
				     ORDER BY v.Installment_no DESC)

Regards,
GVPrabu

MAX function selecting just 10 values but values are more than 10. help me with this

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0