CODEWITHSUNDEEP
sqlsql-server
unnikrishnan
unnikrishnan

Reputation: 151

Fetching most recent data from the table in sql server

I have column called rate in the table which will be updated for each contracts.Every single contract will have multiple rates. So when I write a query by joining multiple tables, I want the rate column to be fetched from the table. but when I run the query it is giving all the rates which are updated.so the contracts are getting duplicated.

I want the query to fetch the most recent updated rate for each contracts. Can anybody suggest an idea of how to do that?

Upvotes: 1

Views: 53

Answers (2)

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

If you have an updatedTime column in contractrate table then you can use ROW_NUMBER and take the latest rate for each contract and join it with contract table.

SELECT c.contractoid ,x.rateamount as ContractRate 
FROM (
       SELECT *,ROW_NUMBER() OVER (PARTITON BY contractoid ORDER BY UpdatedTime DESC) AS RN
       FROM contractrate ) x
INNER JOIN dbo.contract C ON x.contractoid=c.contractoid
WHERE x.RN=1

Upvotes: 1

Naveed Ahmed
Naveed Ahmed

Reputation: 493

you need to save updatedatetime in your rates table and then get query with (Top 1) clause order by updatedate desc.

hope this help

Upvotes: 0

Related Questions