Select most recently used product groups

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 2008 Forums

Transact-SQL (2008)

Select most recently used product groups

Author

Topic

AllanRidley
Starting Member

5 Posts

Posted - 2012-08-13 : 05:19:47

Hi All

I have a database that has (amongst others)
Products Table
--------------
ID
Name

Sales Table
--------------
ID
ProductID
DateSold

I want to pull a report which shows the last 3 products sold (last based on the sold date). I don't want any productIDs duplicated, though, so if the sales table looks like this:
ID ProductID DateSold
1 2 2012-08-13 0:04
2 2 2012-08-13 0:03
3 3 2012-08-13 0:02
4 1 2012-08-13 0:01

I would like my report to show
ProductID
2
3
1

Not
ProductID
2
2
3

Does that make sense?

Thanks!

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-13 : 06:27:01

DECLARE @Products TABLE (ID INT, ProductID INT, DateSold DATETIME)

INSERT INTO @Products
SELECT 1, 2, '2012-08-13 0:04' UNION ALL
SELECT 2, 2, '2012-08-13 0:03' UNION ALL
SELECT 3, 3, '2012-08-13 0:02' UNION ALL
SELECT 4, 1, '2012-08-13 0:01'

SELECT ProductID
FROM @Products
GROUP BY ProductID
ORDER BY Max(DateSold) DESC

--------------------------
http://connectsql.blogspot.com/

AllanRidley
Starting Member

5 Posts

Posted - 2012-08-13 : 06:54:01

Perfect! Thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources