• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

Write a sql statement in SQL2000 to pull the lowest priced items

I need to pull items/records that have the lowest price from a table with multiple distributors based on these three fields, "price" of course, and then "mfgID" and "UPC".  The table has many fields that I will keep for that record with the lowest price.  Some items may only have one occurrence in the table while others may have many so for those with just one occurrence I would keep but for the repeated entires I would keep only the lowest price.

Thanks in advance for your help
0
BlueKnight66
Asked:
BlueKnight66
  • 2
  • 2
  • 2
1 Solution
 
YZlatCommented:
do you mean the lowest price in the table or something liek 5 or 10 lowest prices?
0
 
YZlatCommented:
SELECT * FROM Table1 WHERE price = (SELECT MIN(price) FROM Table1)
0
 
BlueKnight66Author Commented:
Thanks for the reply BUT that won't help me because there are multiple products in the table.  Your selct staement would only return the product with the lowest price regardless of the item number.  I need that type of query BUT with a grouping by UPC+mfgID
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SharathData EngineerCommented:

check this one. If you still have issues, then provide the table structure or sample data with expected result.
SELECT t1.*
  FROM YourTable t1
  JOIN (SELECT MfgID,UPC,MIN(Price) FROM YourTable GROUP BY MfgID,UPC)t2
    ON t1.MfgID = t2.MfgID AND t1.UPC = t2.UPC AND t1.Price = t2.Price

Open in new window

0
 
BlueKnight66Author Commented:
Thank you Sharath 123 and to all those that contributed
0
 
SharathData EngineerCommented:
most welcome.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now