DISTINCT product and Minimum price

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

Transact-SQL (2000)

DISTINCT product and Minimum price

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-08-22 : 06:11:34

Hello,
i have this VIEW, that selects products from two tables, i have edited some of the columns to make it shorter and readble

1. a unique product : dbo.shops_products.Product AS product1
2. Minimum price : MIN(dbo.shops_products.unit_price) AS unit_price

both in red below.

NOW: Some products could appear in the products table more than once, in cases where they have discounts or variations in specifications.

However, i would like to be able to SELECT a DISTINCT Product with the minimum price in my products table.

any advice

thanks
Afrika

here is my code (EDITED) What am i doing wrong here ?



SELECT DISTINCT 
                      dbo.shops_products.Product AS product1, dbo.shops_products.product_FK AS product, MIN(dbo.shops_products.unit_price) AS unit_price, 
                      dbo.shops_products.min_purchase
FROM         dbo.shops_product_registration INNER JOIN
                      dbo.shops_products ON dbo.shops_product_registration.Product_PK = dbo.shops_products.product_FK
WHERE     (dbo.shops_product_registration.suspension = 1)
GROUP BY dbo.shops_products.Product, dbo.shops_products.product_FK, dbo.shops_products.unit_price, dbo.shops_products.min_purchase

LazyDragon
Starting Member

30 Posts

Posted - 2006-08-22 : 06:16:45

You dont need to GROUP BY dbo.shops_products.unit_price and dbo.shops_products.min_purchase

LazyDragon
T-SQL Programmer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 06:46:30

[code]SELECT dbo.shops_products.Product AS product1,
dbo.shops_products.product_FK AS product,
MIN(dbo.shops_products.unit_price) AS unit_price,
dbo.shops_products.min_purchase
FROM dbo.shops_product_registration
INNER JOIN dbo.shops_products ON dbo.shops_product_registration.Product_PK = dbo.shops_products.product_FK
WHERE dbo.shops_product_registration.suspension = 1
GROUP BY dbo.shops_products.Product,
dbo.shops_products.product_FK,
~~dbo.shops_products.unit_price,~~
dbo.shops_products.min_purchase[/code]

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-22 : 06:55:35

Somthing like this


SELECT		dbo.shops_products.Product AS product1,
		dbo.shops_products.product_FK AS product,
		MIN(dbo.shops_products.unit_price) AS unit_price, 
		dbo.shops_products.min_purchase
FROM		dbo.shops_product_registration
INNER JOIN	dbo.shops_products ON dbo.shops_product_registration.Product_PK = dbo.shops_products.product_FK
WHERE		dbo.shops_product_registration.suspension = 1
		And shops_products.product_FK = ( Select Max(shops_products.product_FK) From shops_products sp Where 
		sp.[PK] = shops_products.[PK]) 
GROUP BY	dbo.shops_products.Product,
		dbo.shops_products.product_FK,
		dbo.shops_products.min_purchase

Replace PK by the primary key.

Chirag

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-08-22 : 13:55:33

Thanks Peter, Chirag,

i actually modified Chirag's query here to suit my needs, i used

And shops_products.product_FK = ( Select Min(shops_products.product_FK) From shops_products sp Where 
		sp.[PK] = shops_products.[PK])

thanks mon ami(s)

Ehi

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-08-22 : 14:53:55

Tested over and over again, its ok now,
thanks once again. The sub query does the magic here.

Afrika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources