Want to Select Records with Minimum value

[rehanemis]

Hello,

I would like to select records based on Minimum values of specific vendor.
Example:

MasterItemID Price PriceDate VendorID
1 2 2/5/14 30
1 3 2/5/14 31
1 7 2/5/14 32
2 3 2/5/14 31
2 2 2/5/14 32

So wants the all columns where price is minimum
example: output required
MasterItemID Price PriceDate VendorID
1 2 2/5/14 30
2 2 2/5/14 32

Thanks and waiting of your response.

Rehan

 

[plog]

What do you expect from this data:

MasterItemID Price PriceDate VendorID
1 2 2/5/14 30
1 7 1/1/14 31
1 2 2/5/14 32
2 3 1/1/14 30
2 5 3/3/14 32


Also, does this dataset have a primary key available? Something that makes each record unique?

 

[rehanemis]

The table has no primary key.

I would like to get whole columns where Price is low against each MasterItemID.

 

[plog]

From my sample data, please show me what data you expect back.

 

[rehanemis]

What i need from your given data set is as below:
MasterItemID Price PriceDate VendorID
1 2 2/5/14 30
1 2 2/5/14 32
2 3 1/1/14 30

In your sample data there rare chance that Price is always same so either in first 2 records we have either 1st record or 2nd based on low price.

 

[plog]

You didn't provide your table name, so replace all instances of 'YourTableNameHere' in the code below with the name of your table.

Now, to achieve what you want you will need a sub-query to determine what the lowest price is per product. This is that SQL:

SELECT MasterItemID, MIN(Price) AS LowestPrice
FROM YourTableNameHere
GROUP BY MasterItemID;

Name that query 'LowestPrice_sub_1'. Then you use it in a query along with YourTableNameHere to find the records that match:

SELECT YourTableNameHere.*
FROM YourTableNameHere
INNER JOIN LowestPrice_sub_1 ON LowestPrice_sub_1.MasterItemID = YourTableNameHEre.MasterItemID AND LowestPrice_sub_1.LowestPrice = YourTableNameHere.Price;

Run that and you will get your results.

 

[rehanemis]

wao.

Thanks!!! i got the desire results.

Thanks again.