-1

Imagine that I have a foreign-key table like this:

Name    ID    Price
Mop     P1    11.99
Mop     P2     9.99
other stuff...........

Now as you can see I have at least two mop products and there's no telling how many products in the table have the same name. I want to however return ONLY ONE of each product with the same name which will be the product with the lowest price for each different product. So in this simple example I want to return the Mop that cost 9.99. How do I go about this?

Shadow
  • 33,525
  • 10
  • 51
  • 64
RudolphRedNose
  • 163
  • 1
  • 6
  • 16

1 Answers1

-1

Of course it exists the MIN() function as @Ferrakem said. But if you cannot use it because of whatever, here is a nice and useful for learning query:

SELECT * FROM tableX
WHERE Price >= ALL(SELECT Price FROM tableX)

Check out that here I’m selecting the price that is same or higher than all the prices in the same list.

Knowing this technique will make you more handful in certain situations. Good luck.

John
  • 165
  • 1
  • 8
  • 1) how could price be greater than all of the price values in the same table? 2) Greater indicates max, not min. 3) There is no grouping – Shadow Dec 09 '18 at 07:43
  • Indeed it was actually <, the explanation is still representative. To your first question, you are lacking a logical concept, check it out on a real sample, you will see it works. And yes, I didn’t group properly, I mistook when all the items in table had the same name. Best regards – John Dec 09 '18 at 08:33