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:
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.