Hi.

Say I have a table with 3 fields, Date, Price and Code. There are many
different priceswith different dates  for each code. I need a join
condition that will add latest prices for each code into some big query
that outputs one row for each code.

The solution I came up with involves subquery e.g.
INNER JOIN Prices ON Prices.Code = BigTable.Code and Prices.DT =
(SELECT MAX(DT) FROm Prices p1 where Prices.Code = p1.Code).


Is there some more efficient solution?

I'm using MSSQL 2005.

....I could also join the table with max dates for each code before
joining prices and use it in join ocndition but that doesn't ocunt as
efficient either :)

realg***@gmail.com wrote:

Use ROW_NUMBER within a Derived Table:

BigTable join
  (
   select
     row_number() over (partition by code
                        order by DT desc) as rnk,
     ...
   from Prices
  ) Prices
on Prices.Code = BigTable.Code
where Prices.rnk = 1

Dieter

Marking (Not just Finding) Duplicate Values ......
Select part of a string
UPDATE in a SQL script file is not working.
Problem with WHERE clause
SET/SELECT truncate string