Re: select query - I want one row to be unique

U see jacob 8p U need to do a correlated query like thism excuse the 666, i find it a lucky number in development !

select * from #test
Select a.product, a.amount, a.date
from #test a
where a.date in (Select max(b.Date)
from #test b where a.product = b.product)

---------------OUTPUT ------------------

Product     amount                date
----------- --------------------- ---------------------------
1           200.0000              2001-01-01 00:00:00.000
1           400.0000              2001-01-02 00:00:00.000
2           666.0000              2001-01-02 00:00:00.000

(3 row(s) affected)

product     amount                date
----------- --------------------- ---------------------------
2           666.0000              2001-01-02 00:00:00.000
1           400.0000              2001-01-02 00:00:00.000

(2 row(s) affected)

Jacob Nordgren <jacob_nordgren_at_hotmail.com> wrote in message news:47c6b9be.0108070125.21f353e0_at_posting.google.com...
> Hi,
>
> I have this data
>
> PRODUCT PRICE MYDATE
> 1 12 2001-08-01
> 1 13 2001-08-07
> 2 14 2001-08-03
>
> I want to get a list of all the products with the latest price only
> (no duplicate products). I know how to do the query without the price
> part:
>
> "SELECT PRODUCT, MAX(MYDATE) FROM T GROUP BY PRODUCT" works fine.
>
> How can I include the PRICE column into the result?
>
> / Jacob
Received on Wed Aug 08 2001 - 01:27:04 CDT