Comparing rows against each other

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

SQL Server Development (2000)

Comparing rows against each other

Author

Topic

token
Posting Yak Master

133 Posts

Posted - 2006-12-23 : 21:32:25

Merry Christmas to you all!!

I was wondering if someone could be point me in the right direction with comparing rows within a table against each other before selecting the lowest value.

Basically I have a table called PRESTAGE that has products in it. There maybe many of the same product in that table, but their prices will be different. But I need to select the lowest priced products to display on my website so I created a new table called STAGE and I want to insert the cheapest products into that table.

Here is how I am inserting data from PRESTAGE into STAGE:
---
INSERT INTO Stage
(Manufacturer
,ManfProductCode
,ProductName
,SupplierID
,SupplierProductCode
,OurPrice
)
SELECT
Manufacturer
,ManfProductCode
,ProductName
,SupplierID
,SupplierProductCode
,OurPrice

FROM dbo.PreStage
---

What I want to do is say insert the cheapest products (based on the ManfProdCode) from PRESTAGE into STAGE if they are not already in STAGE. That is, compare the OurPrice column for all rows in PRESTAGE with the same ManfProdCode and then insert those rows into the STAGE table.

The logic is that SQL will compare each row in the PRESTAGE table that have the same ManfProdCode by the OurPrice field and then insert the results into the STAGE table.

Any ideas how to do this? I cant find the answers in any of my SQL books on how to select the lowest value by comparing rows within a table.

I much appreciate your help and time. Merry Christmas again!

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-24 : 07:17:18

What is the primary key of the Table PreStage???

Try out this query


INSERT INTO Stage
(Manufacturer
,ManfProductCode
,ProductName
,SupplierID
,SupplierProductCode
,OurPrice
)
SELECT
b.Manufacturer
,a.ManfProductCode
,b.ProductName
,b.SupplierID
,b.SupplierProductCode
,a.OurPrice

FROM 
(
	Select Min(PK) As PK,Min(OurPrice) OurPrice, ManfProductCode From 
	PreStage Group by ManfProductCode 
) As a
Inner Join PreStage b on b.pk = a.pk 
Where 
	Not Exists 
			(
				Select * From Stage s Where s.ManfProductCode = a.ManfProductCode
			)

Replace PK with the primary key column of the Table PreStage.

Chirag

http://chirikworld.blogspot.com/

token
Posting Yak Master

133 Posts

Posted - 2006-12-24 : 07:37:58

Amazing stuff Chirag! It worked. I can't thank you enough. Do you have a paypal account? If you can mail me your paypal details I want to send you a gift for you time and help. I am very thankful to you.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-25 : 02:28:09

Aha.. Happy to know that it solved your issue..

Wishing you a Merry Christmas too..

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources