returning partially distinct/unique rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

returning partially distinct/unique rows

Author

Topic

andrej351
Starting Member

6 Posts

Posted - 2009-05-11 : 22:03:15

Hi all,

I need to create a query which groups by two columns and returns an additional column based on a condition.

For example, say I've got the following columns:

ProductCode | SerialNumber | Quantity | DatePurchased | CustomerID

and the table contains duplicate combinations of ProductCode and SerialNumber with differing Quanitites and Purchase Dates. I'd like to return the ProductCode, SerialNumber and the Quantity for the row with greatest (most recent) value for DatePurchased. To further complicate things this must be done for all rows where CustomerID = 'xxx'.

Any ideas???

Any help appreciated.
Cheers.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 22:06:25

[code]
SELECT TOP 1 ProductCode, SerialNumber, Quantity
FROM TABLE
WHERE CustomerID = 'xxx'
ORDER BY DatePurchased DESC
[/code]

KH
[spoiler]Time is always against us[/spoiler]

andrej351
Starting Member

6 Posts

Posted - 2009-05-11 : 22:17:47

i understand your answer, but that would return me 1 valid row whereas i need all the rows which match the criteria.

Cheers.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 22:22:13

[code]
SELECT t.ProductCode, t.SerialNumber, t.Quantity
FROM TABLE t
INNER JOIN
(
SELECT CustomerID, DatePurchased = MAX(DatePurchased)
FROM TABLE
GROUP BY CustomerID
) m on t.CustomerId = m.CustomerID and t.DatePurchased = m.DatePurchased
WHERE CustomerID = 'xxx'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-05-11 : 22:39:57

Select ProductCode,SerialNumber,Quantity 
from
(Select ProductCode,SerialNumber,Quantity,DENSE_RANK() OVER
(PARTITION BY ProductCode,SerialNumber ORDER BY DatePurchased DESC)as SEQ from TABLE)Z
Where Z.SEQ = 1

andrej351
Starting Member

6 Posts

Posted - 2009-05-12 : 00:35:01

Final solution:


SELECT a.*
FROM Table as a
JOIN (
   SELECT ProductCode, SerialNumber, MAX(DatePurchased) as MaxDate
   FROM Table   GROUP BY ProductCode, SerialNumber) as b ON
       a.ProductCode = b.ProductCode AND
       a.SerialNumber = b.SerialNumber AND
       a.DatePurchased = b.MaxDate
WHERE   a.CustomerID = 'xxx'

Thanks guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources