Simple SQL Max query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Simple SQL Max query

Author

Topic

xee2fly
Starting Member

3 Posts

Posted - 2012-07-05 : 06:40:05

I need to write a SQL statement from these tables that will return the Product Name and Quantity with the maximum quantity sold per day, but not getting it right. I'm thinking that I need a subquery, but I'm sure there are different ways of doing it.
Transactions:
• Sales Order
• Order Quantity
• Order Total
• Order Date
• SalesPersonID
• ProductID
AND

Product
• ProductID
• ProductName

Any ideas?
Thank you!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-05 : 06:45:25

What have yoou tried - that will give us a clue as to what is in the tables.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

xee2fly
Starting Member

3 Posts

Posted - 2012-07-05 : 07:05:28

I've tried this script, but it is clearly missing something.

select dbo.Trans$.[Order Date] , dbo.Product$.ProductName,max(dbo.Trans$.[Order Qty]) maxqty
FROM dbo.Trans$ INNER JOIN dbo.Product$ ON dbo.Trans$.ProductID = dbo.Product$.ProductID
group by dbo.Trans$.[Order Date], dbo.Product$.ProductName

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-07-05 : 07:41:46

[code]
-- *** Test Data ***
CREATE TABLE #Trans
(
SalesOrder int NOT NULL
,OrderQuantity int NOT NULL
,OrderDate datetime NOT NULL
,ProductID int NOT NULL
);
INSERT INTO #Trans
SELECT 1, 2, '20120701', 1
UNION ALL SELECT 2, 5, '20120701', 1
UNION ALL SELECT 3, 3, '20120701', 1
UNION ALL SELECT 4, 5, '20120701', 2
UNION ALL SELECT 5, 10, '20120701', 2
UNION ALL SELECT 6, 7, '20120702', 1
UNION ALL SELECT 7, 2, '20120702', 1;

CREATE TABLE #Products
(
ProductID int NOT NULL
,ProductName varchar(20) NOT NULL
);
INSERT INTO #Products
SELECT 1, 'Product1'
UNION ALL SELECT 2, 'Product2';
-- *** End Test Data ***

SELECT T.OrderDate, P.ProductName, T.OrderQuantity
,MAX(T.OrderQuantity) OVER (PARTITION BY T.OrderDate, T.ProductID) As MaxQuantity
FROM #Trans T
JOIN #Products P
ON T.ProductID = P.ProductID;
[/code]

xee2fly
Starting Member

3 Posts

Posted - 2012-07-05 : 08:00:19

Hi Ifor,

That works like a charm.
I'm getting back into SQL, so seems all new to me again.
What does this part of the query mean?:
OVER (PARTITION BY T.OrderDate, T.ProductID) As MaxQuantity

Thanks!

quote:

Originally posted by Ifor


-- *** Test Data ***
CREATE TABLE #Trans
(
	SalesOrder int NOT NULL
	,OrderQuantity int NOT NULL
	,OrderDate datetime NOT NULL
	,ProductID int NOT NULL
);
INSERT INTO #Trans
SELECT 1, 2, '20120701', 1
UNION ALL SELECT 2, 5, '20120701', 1
UNION ALL SELECT 3, 3, '20120701', 1
UNION ALL SELECT 4, 5, '20120701', 2
UNION ALL SELECT 5, 10, '20120701', 2
UNION ALL SELECT 6, 7, '20120702', 1
UNION ALL SELECT 7, 2, '20120702', 1;

CREATE TABLE #Products
(
	ProductID int NOT NULL
	,ProductName varchar(20) NOT NULL
);
INSERT INTO #Products
SELECT 1, 'Product1'
UNION ALL SELECT 2, 'Product2';
-- *** End Test Data ***

SELECT T.OrderDate, P.ProductName, T.OrderQuantity
	,MAX(T.OrderQuantity) OVER (PARTITION BY T.OrderDate, T.ProductID) As MaxQuantity
FROM #Trans T
	JOIN #Products P
		ON T.ProductID = P.ProductID;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 09:46:01

it horizontally partitions table into groups based on values of columns T.OrderDate, T.ProductID and then MAX will return maxorder quantity within each group. So effectively you will get maximum order quantity for product for that day along with each record

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources