Nth smallest price by row

[jrhessey]

I am in need of some assistance. I've been looking on here for an answer, but can only find solution that would give me a by column lowest price, and I need to compare the prices by row. What I have is a table of vendor prices for part numbers we sell. What I want to do is compare all the prices the vendors for each part number and return the lowest and 2nd lowest fields in a query. Here is an example...

Part # vendor 1 vendor 2 vendor 3 Lowest Vendor Price Low Vendor #
10526 .75 .97 .67 .67 3
10527 .60 .82 .85 .60 1


Thanks for any ideas....

 

[Rich]

Try and normalis(z)e the data first

 

[jrhessey]

Not sure what you mean by normalize my data? Do you mean normalize lie this?

Part # | vendor 1 | vendor 2 | vendor 3 | Lowest Vendor Price | Low Vendor #
10526  | .75      | .97      | .67      |    .67              |   3
10527  | .60      | .82      | .85      |    .60              |   1

Thanks!

 

[The_Doc_Man]

Google-search "Database Normalization" and read some of the articles. Your data set is not normalized in the database sense of the word.

For the articles on that search, stick to .EDU sites for colleges and universities you know, plus any vendors you know and trust.

 

[raskew]

Hi -

Your data is stored in a spreadsheet configuration. It should be stored in something of this variety:

[i]tblVendor[/i][b]
ItemID	PartID	VendorID  Price[/b]
1	10526	Vendor1	  $0.75
2	10526	Vendor2	  $0.97
3	10526	Vendor3	  $0.67
4	10527	Vendor1	  $0.60
5	10527	Vendor2	  $0.82
6	10527	Vendor3	  $0.85

In the above configuration, a query would return the desired result:

SELECT
    t.PartID
  , tblVendor.VendorID
  , tblVendor.Price
FROM
   tblVendor AS t 
LEFT JOIN
   tblVendor 
ON
   t.PartID = tblVendor.PartID
GROUP BY
   t.PartID
  , tblVendor.VendorID
  , tblVendor.Price
HAVING
   (((tblVendor.Price) In (
SELECT
    Top 1 [Price] 
FROM
     tblVendor WHERE[tblVendor].[PartID] = t.partID 
ORDER BY
     [Price])));

Which returns:

[b]
PartID	VendorID  Price[/b]
10526	Vendor3	  $0.67
10527	Vendor1	  $0.60

HTH - Bob