GroupBy help plz

[KiZa7]

Hi

I wanna do something similar to what Stephanie.D tried to do before..I read the suggestion she recieved but doesn't seem to work for me. I have one table with the following

==Id==TypeofProd==Name==SpecNameofProd==From==Price==

I'd like to create a query that only returns the rows of the table where the Price of the Name is the smallest. Here's an example...
==Id==TypeofProd==Name==SpecNameofProd==From==Price==
1 drinks wine Porto Store1 5
2 drinks wine Porto Store2 6
3 food bread Baguette Store3 1
4 food bread Integral Store3 2
5 drinks wine Bordeaux Store3 3
It should return
==Id==TypeofProd==Name==SpecNameofProd==From==Price==
3 food bread Baguette Store3 1
5 drinks wine Bordeaux Store3 3

I tried doing a GroupBy but it only works when for the query I only use the SpecNameOfProd and Price fields. I tried using Where like it was suggested to Stephanie but I need the other fields to show...

Thx! for any help and suggestions

 

[Moniker]

Put all the fields on Group By except for the price field. Set that to Min.

 

[KiZa7]

still not working...

"I tried doing a GroupBy but it only works when for the query I only use the SpecNameOfProd and Price fields. I tried using Where like it was suggested to Stephanie but I need the other fields to show..." Here where I put SpecNameOfProd I meant Name.

Moniker, it doesnt seem to work that way, it just shows all the table without any querying...
I need it to group by only the Name field using Min of Price, and then show all the details related to the Name, liek SpecName, From...Like having two wines with different prices, he should choose the cheapest and show what Brand and where it was bought.

 

[namliam]

You do this using 2 query's

1)
Select name, min(price) as MinPrice
from table
group by name

2)
Join above query with your original table on both name and price showing the columns you need....

Hope this helps

 

[KiZa7]

Thx!

Thanks a lot namliam for your help. Works just like it should that way..

Take care