0

This question already has an answer here:

Suppose I have:

Product(_id,Name)

Provider(_id,Name)

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: What seemed to be happening behind the scenes

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).

marked as duplicate by Paul White, ypercubeᵀᴹ, RolandoMySQLDBA, Philᵀᴹ, Kin Shah Oct 8 '14 at 21:27

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • What database system do you use? can you add an appropriate tag? At least in oracle this query will not work an raise an error because the "select" list may only contain expression from the "group by" list or expression of aggregate functions. – miracle173 Sep 16 '14 at 6:12
  • I think using fields in a "selec"t list that are not in the "group by" list is not sql standard and as far as I know mysql is the only database that allows this. I think mysql uses the colun of an arbitrary record of the group but I think you should simply not use this features because it is not standard. – miracle173 Sep 16 '14 at 6:21
  • agreed with @miracle173, please add database system in tag – vijayp Sep 16 '14 at 6:25
  • It was an exam question I implemented on sqliteman. So you're telling me sqlite somehow works this out, though this is not standard? – Ademord Sep 16 '14 at 6:32
  • Your question (bold text) is not clear by the way. Too vague. What does "providers who sell the most expensive products" mean? The providers who sell the 10 most expensive products? The 100 most expensive? The providers who sell all their products in higher prices than other providers? The providers who sell at least one product higher than all other providers of the same product? – ypercubeᵀᴹ Sep 16 '14 at 10:21
1

(this grew a little long to be a comment)

As miracle173 says, this is not standard SQL as there are fields in the SELECT clause that are neither in the GROUP BY or in an aggregation function, meaning their values are undefined: for any given piece.Name there could be any number of prices and providers by there is only space to output up to one. This means the database engine must arbitrarily be picking one. It might be the first as per the ORDER BY, but then what if there are two providers with the same piece at the same price?

If it is picking arbitrarily what goes into those values (which is must be if it doesn't just error) then the output is unstable: it might arbitrarily pick something else next time around because it chooses a different query plan as your data grows.

So for your overall question "How does this work?" my answer would "It shouldn't...".

  • SQLite allows such random fields only for bug compatibility with MySQL. (It would be different if there were a min() or max().) – CL. Sep 16 '14 at 10:52
  • But for this query min and max would probably not produce useful results: you won't always get the supplier that matches the price. The query needs a rework basically. In MSSQL, Postgres, Oracle or similar the windowing functions would be the way so go, but I don't thing sqlite supports these. (edit: just looked at your link and that suggests sqlite could pick a reasonable pair of values, but I wouldn't rely on (or even ever learn) non-standard behaviour like that as it'll not work if you move databases) – David Spillett Sep 16 '14 at 12:49
  • Hey, I detailed some things! – Ademord Sep 16 '14 at 15:54
1

The order of execution for SQL query is

FROM & JOIN -determine table & filter rows
WHERE -more filters on the rows
GROUP BY -Combine rows in group
HAVING - filters on group
ORDER BY - arrange the remaining rows

Does group by get a random record from the table ?

Answer is No.

Is Order by working first and then Group by gets the first record?

Answer is No.

How the query execution happen will tell us the reason, In the query that you have added, FROM provider,piece,provides is executed first, then

WHERE provider._id = provides.ProviderId
AND piece._id = provides.PieceId
AND provider.Name LIKE 'A%'
after that GROUP BY followed by ORDER BY clause.

EDIT:

Check Sqlite select for more details.

  • I edited my question to clarify some things! – Ademord Sep 16 '14 at 5:47
  • the OP's query will not work on most database systems. On mysql it will run and then it is a "random" record. – miracle173 Sep 16 '14 at 6:27

Not the answer you're looking for? Browse other questions tagged or ask your own question.