Click here to Skip to main content
15,942,710 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hai Every One,

I have a table like

Columns are
1.Loantype
2.Rate OF Interest
3.Days
4.EffectiveDate

These are the rows for that table

CSS
1   12  30  2011-11-24 00:00:00.000
1   14  60  2011-11-25 00:00:00.000
3   14  60  2011-10-11 00:00:00.000
2   12  30  2011-11-24 00:00:00.000
3   11  120 2011-11-25 00:00:00.000
4   15  60  2011-11-26 00:00:00.000
2   14  60  2011-11-25 00:00:00.000
4   11  30  2011-11-26 00:00:00.000
4   12  60  2011-11-27 00:00:00.000
3   12  120 2011-11-22 00:00:00.000
4   14  90  2011-11-30 00:00:00.000
4   16  120 2011-11-30 00:00:00.000
2   14  60  2011-11-25 00:00:00.000
1   10  30  2012-01-01 00:00:00.000
1   15  30  2012-01-03 00:00:00.000
3   16  30  2012-01-01 00:00:00.000
2   10  30  2012-01-01 00:00:00.000
2   15  60  2012-01-04 00:00:00.000



i want to select latest rate of interest for every loan type
ex:
for loan type 1 has 2 rate of interest for 30 days i want latest one i.e. 10 on 2012-01-03
My result should like this
1	15	30	2012-01-03 00:00:00.000
     1	14	60	2011-11-25 00:00:00.000
     2	14	60	2011-11-25 00:00:00.000
     2	14	60	2011-11-25 00:00:00.000
     3	16	30	2012-01-01 00:00:00.000
     3	14	60	2011-11-12 00:00:00.000


Please help me write select state for above result set

Thanks In Advance
Posted

How about:

SQL
;with [cte] as 
(
select 
    Loantype, 
    Days,
    [Rate OF Interest], 
    rowNumber = row_number() over (partition by Loantype, Day order by EffectiveDate desc)
from [table]
)
select 
    Loantype,
    Days,
    [Rate OF Interest]
from [cte]
where rowNumber = 1
 
Share this answer
 
Comments
Amir Mahfoozi 12-Jan-12 9:42am    
+5 Exact answer.
[no name] 12-Jan-12 16:53pm    
Just keep in mind Common Table Expressions are only available with SQL Server 2005 and above. The OP didn't specify what version was being used.
[no name] 12-Jan-12 23:59pm    
thank You very much.
I got the result.
First time i learned the concept about CTE.
Once again thank you
SQL
SELECT LAST(Rate_Of_Interest) FROM TABLE WHERE Loantype = 1.

What will retrieve the lastest rate of interest value for a given loantype.

But if you want to know the lastest, in the meaning of the last EffectiveDate as I think you are trying to explain, you can simply do:

For a given loantype:

SQL
SELECT MAX(EffectiveDate)FROM TABLE WHERE Loantype = 1


For every one:

SQL
SELECT * FROM TABLE WHERE EffectiveDate = (SELECT MAX(DATE) FROM TABLE)



You will ned the effective date as column type date, so the string can be parsed (as date obviously)

You can also do a stored procedure so you can retrieve the present date (using getDate) and get you can find the difference using DATEDIFF.

More info http://www.sql-server-performance.com/2007/datetime-2008/[here]



Let me know if this helps you.
 
Share this answer
 
v3
May not be the best, but off the top of my head

SQL
SELECT [columns] FROM [table]
WHERE Date = (SELECT MAX(date) FROM table)
 
Share this answer
 

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