GROUP BY min price BUT return primary key of row

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

GROUP BY min price BUT return primary key of row

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2013-10-24 : 06:32:26

Hello

I have a table with multiple products from different suppliers. there will duplicate products in my db as Supplier A may stock the same product as Supplier B. I have a business rule that only one unique product can be online at one time and as such I want the cheapest product from the 2 Suppliers to be displayed where there is a match.

I have been trying a Group By clause and also a partition by but my problem is returning the primary key for the winning row (minimum price).

here is an example

CREATE TABLE #TempTable(
productcode nvarchar(50),
productname varchar(50),
productmanufacturer varchar(50),
saleprice money,
tyrewidth varchar(5),
tyreaspectratio varchar(5),
tyrerimdiameter varchar(5),
stockstatus int
)

Select productmanufacturer, productname , MIN(saleprice) AS saleprice,
tyrewidth, tyreaspectratio, tyrerimdiameter from #TempTable
where stockstatus >= 4
GROUP BY
productmanufacturer, productname,
tyrewidth, tyreaspectratio, tyrerimdiameter
HAVING
COUNT(*) > 1

How can I return the productcode in the above query for the row with the cheapest price? If I try MIN(productcode) or MAX(productcode) it will not always return the product code associated to the min price

thanks,

Matt

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-10-24 : 07:44:21

There Is No ( stockstatus ) ColumnName In #TempTable

veeranjaneyulu

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2013-10-24 : 08:06:18

sorry, just modified the post.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-24 : 08:24:19

[code]
SELECT productcode ,productmanufacturer, productname , saleprice,
tyrewidth, tyreaspectratio, tyrerimdiameter
FROM
(
Select productcode ,productmanufacturer, productname , saleprice,
tyrewidth, tyreaspectratio, tyrerimdiameter,
ROW_NUMBER() OVER (PARTITION BY productname ORDER BY saleprice) AS Seq
from #TempTable
where stockstatus >= 4
)t
WHERE Seq=1
[/code]

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

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2013-10-24 : 09:12:20

fantastic. thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-24 : 11:43:16

welcome

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