From: "Jack" Newsgroups: comp.databases.oracle.misc,comp.databases.ms-sqlserver,comp.databases.theory,comp.databases.oracle.server,microsoft.public.sqlserver.programming Subject: Re: select query - I want one row to be unique Date: Wed, 8 Aug 2001 14:27:04 +0800 Organization: Pacific Supernet Limited Lines: 51 Message-ID: <9kqm88$sad$1@hfc.pacific.net.hk> References: <47c6b9be.0108070125.21f353e0@posting.google.com> NNTP-Posting-Host: ppp45.dyn11.pacific.net.hk X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 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 wrote in message news:47c6b9be.0108070125.21f353e0@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