February 19, 2008 at 1:10 pm
want to select distinct product using maximum value of tran date
data like:
Table Product Table 2
parent product sub product date product descrip
1 11 01/01/03 1 this is it
1 45 01/15/04 4 abc
1 47 09/30/05
1 62 06/24/06
1 89 02/04/08
4 23 02/27/04
4 09 02/13/05
The data that I want to retrieve is
distinct parent_product, sub product, descrip using the max value of date.
desired results
1,89,"This Is It'
4,09,"abc"
Can anyone give me an idea how to do this?
February 19, 2008 at 9:18 pm
Is this what you are looking for?
SELECT DISTINCT parent_product, sub product, descrip
FROM TABLENAME
WHERE CONVERT(VARCHAR(10),DATE,111)=(SELECT MAX(DATE)FROM TABLENAME)
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
February 20, 2008 at 12:37 am
The problem with SELECT DISTINCT is that it returns a distinct combination of all selected columns, so if you are just looking to return distinct based on the first column, it throws you off the mark.
A workaround is to use[Code]
SELECT a.Column1, a.Column2, ..., a.columnX
FROM Table1 a
INNER JOIN
(SELECT DISTINCT COlumn1
FROM Table1) b
ON a.Column1 = b.Column1[/Code]
February 20, 2008 at 1:42 am
This should do what you want.
The subquery gets the max date for each parent product. Then join the subquery back to the table on both columns to get the rest of the data
SELECT Product.parent_product, Product.sub product, Product.descrip
FROM Product INNER JOIN
(SELECT MAX(Product_date) AS MaxDate, Parent_product
FROM Product
GROUP BY Parent_product) Sub
ON Product.Parent_product = Sub.Parent_product AND Product.Product_date = Sub.MaxDate
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2008 at 6:19 am
Super....
Thanks, sometimes a little thing when stared at long enough becomes a mountain range.
February 20, 2008 at 6:42 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply