Click here to Skip to main content
15,848,319 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Im having table with crores of records. The table consists of Lakhs of Loan Numbers. Each Loan Number consists of 12 to 60 Instalments.

My query is to find the Loan Number which has the Instalment Amount in descending order.

Each Loans having different patterns of Instalment. Some Loans have same amount for all instalments. Some loans having higher amount then the previous one (ie)Instalment Amount should be in ascending order. Some loans amount should be differs for each Instalment. How to find the loan where the amount should be decreased for each Instalment.

For Example

Loan InsNo InsAmount
A 1 500
A 2 500
B 1 700
B 2 400
B 3 800
C 1 400
C 2 300
C 3 200

My required output should be C

In above example loan A has two Instalment and both Instalment have same Amount 500.
For loan C it has three Instalment and each amount varied and also in desending order 400,300,200.. In my whole table i need C type of loans.
Posted
Updated 30-Jan-14 0:28am
v3
Comments
Bernhard Hiller 30-Jan-14 4:00am    
Try again to describe the requirements. Neither do I uderstand the line
"For first Instalment the amount should be 5000 and second should be 4500 and so on"
nor
"My output should be C"
Use the green "Improve question" link
Aravindba 30-Jan-14 4:09am    
Actually ,what is ur question ? u need to get data in descending order ? or need to get first 3 maximum loan amount ,try to clearly specify,pls update ur question again.

If you don't mind about my very rusty SQL (moreover I had to use SQLite so no SQL Server goodies, here), please follow me:

The many views:
SQL
create view  o  as select *  from dat order by Loan, InsAmount desc;
create view o1  as select *, (select count(*)+1  from o as b where a.Loan=b.Loan and a.InsAmount <b.insamount)>select * from o1;
select "----------------------------";
create view o2 as select distinct(Loan) from o1 where InsNo<>RowNo;
select * from o2;
select "----------------------------";
select distinct(Loan) from dat where Loan not in (select Loan from o2);
The output:
A|1|500|1
A|2|500|1
B|3|800|1
B|1|700|2
B|2|400|3
C|1|400|1
C|2|300|2
C|3|200|3
----------------------------
A
B
----------------------------
C

As you might see the last query gives the wanted result.
  • View o1 provides the new field RowNo that strictly orders by InsAmount rows having the same Loan
  • View o2 reports the Loans where there is a InsNo-RowNo mismatch.
  • The last select takes the complement of o2.


With a better knowledge of SQL, you can get rid of most of these views, I guess. Moreover, having SQL Server, you could use the ROW_NUMBER fucntion shortcut.
 
Share this answer
 
v3
Comments
kirthiga S 30-Jan-14 7:19am    
Thank u Pallini.. The Eqivalent SQL 2008 query is

select Loan,InsNo,InsAmount,ROW_NUMBER() over(partition by Loan order by InsAmount )Rn into #1
from vary where InsNo<>0 order by Loan,InsAmount

select distinct Loan into #2 from #1 where InsNo-Rn<>0

select distinct a.Loan into #3 from a left join #2 b on a.Loan=b.Loan where b.Loan is null
CPallini 30-Jan-14 8:19am    
You are welcome.
Hi I think u need datas in descending order pls try this query
SQL
SELECT Loan, SUM(InsAmount) AS aaa FROM tttt GROUP BY Loan ORDER BY aaa DESC 'This is desc if u need asc simply change desc to asc or remove desc

Regards,
Aravind
 
Share this answer
 
v4
Comments
kirthiga S 30-Jan-14 4:21am    
I dont need data in descending order. I want the list of Loan Numbers where the Instalment amount in descending order.
Aravindba 30-Jan-14 4:29am    
u need to sum of Loan and desc order ? like C total is 900 ,B total is 1900 and A total is 1000.
ur ouput is total installment amount in descending order
C 900
A 1000
B 1900

in that can get only total installment amount for each Loan and desc oreder

pls check above answer,modified
SQL
select distinct t1.loan from tablename t1 where
(
(select top 1 t2.insno from tablename t2
where t2.loan = t1.loan order by t2.insamount desc
)=1
)
 
Share this answer
 
v3
Hi,

Check this Once

Select Loan, InsNo, InsAmount from LoanDetails
Order By Loan asc, InsNo Desc
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900