How to find data in descending order

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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 29-Jan-14 20:14pm

kirthiga S

Updated 29-Jan-14 23:28pm

v3

Add a Solution

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.

4 solutions

Solution 1

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

Posted 29-Jan-14 22:15pm

Aravindba

Updated 29-Jan-14 23:31pm

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

Solution 3

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
)

Posted 29-Jan-14 23:27pm

Peter Leow

Updated 29-Jan-14 23:30pm

v3

Solution 2

Hi,

Check this Once

Select Loan, InsNo, InsAmount from LoanDetails
Order By Loan asc, InsNo Desc

Posted 29-Jan-14 22:41pm

subbuksr123

Updated 29-Jan-14 22:52pm

v2

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)

CPallini · Accepted Answer · 2014-01-29T23:30:00

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.