This question already has an answer here:
Suppose I have:
Provides(ProductId, ProviderId, Price)
If I want to obtain the providers who sell the most expensive products, and whose name starts with A.
- Could you tell me how the following snippet runs? It seems to be returning the results I ask for.
- Does group by get a random record from the table ?
- Is Order by working first and then Group by gets the first record?
SELECT provider.Name, piece.Name, provides.Price FROM provider,piece,provides WHERE provider._id = provides.ProviderId AND piece._id = provides.PieceId AND provider.Name LIKE 'A%' GROUP BY piece.Name ORDER BY provides.Price DESC
Edit1: I still haven't gotten answered if the way this query is made, my question (bold text) is answered. I'm ALSO asking for what happens once/behind the scenes Group by is applied. See I'm trying to order based on price. I'm not asking about the basic idea, but what's going on behind. I got answered "No." So what happens behind the Group by and over who Order by works over?
Edit2: Seems the question was a bit vague: The original question, for clarification, meant:
For every product, I want the provider who sells it the most expensive.
So can you guys suggest solutions with standard SQL? What was on my mind, along with the results giving me the right things was:
So, does anyone know why group by doesn't internally keep the records "grouped" and then order by sorts them? and shows instead an arbitrary record? (meaning for this problem the solution I had in mind is not trustworthy/predictable).