Query issue - Northwind - 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

SQL Server 2012 Forums

Transact-SQL (2012)

Query issue - Northwind

Author

Topic

michal_milena
Starting Member

1 Post

Posted - 2014-05-11 : 11:36:03

Hello I'm new here, so I hope that it's good place for ask:)

Generally I'm trying to refresh my knowledge of t-sql after 3 year. I'm learning on Microsoft Northwind model base - I cannot resolve one query.

I want fix data from 3 tables - Order Details, Orders, and Products. Becouse I want to retrieve - which product is the most expensive in each order.

To get the most expensive prices in orders is simply, becouse I can use this query:

SELECT
OrderID, MAX(UnitPrice)
AS
Max
FROM
[order details]
GROUP
BY
OrderID

But If I want to add one more column, as name of product, I would able do something like this:

SELECT
OrderID, MAX(UnitPrice)AS Max, OD.UnitPrice
FROM
[order details] as OD join Products as P ON OD.ProductID = P.ProductID
GROUP
BY
OrderID, OD.UnitPrice

But it mess everything - it don't give result:(
I don't know how to resolve it? I suppose I need use subquery, but how?

I will be appreciated for any help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-11 : 20:48:28

Here are various method to do that. Subquery is the least prefer method.

-- Using derived table
select	*
from	[order details] d
	inner join
	(
		SELECT	OrderID, MAX(UnitPrice) AS MaxPrice
		FROM	[order details]
		GROUP BY OrderID
	) m	on	d.OrderID	= m.OrderID
		and	d.UnitPrice	= MaxPrice

-- Using window ranking function
select	*
from	(
		select	*, rn = row_number() over (partition by OrderID order by UnitPrice desc)
		from	[order details]
	) d
where	d.rn	= 1

-- using subquery
SELECT	OrderID, MAX(UnitPrice) AS Max,
	(select top 1 ProductID from [order details] x where x.OrderID = d.OrderId order by UnitPrice desc) as ProductID
FROM	[order details] d
GROUP BY OrderID

You can also use APPLY operator to achieve this.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-05-12 : 07:11:55

Another way using EXISTS


SELECT OD.OrderID,ProductName,OD.UnitPrice
FROM [order details] as OD 
join Products as P 
ON OD.ProductID = P.ProductID
WHERE NOT EXISTS (SELECT 1
FROM [order details]
WHERE OrderID = OD.OrderID
AND UnitPice > OD.UnitPrice
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources