-1

After creating the following table, I want to extract the names of the countries with the lowest Quant on each continent - but I keep getting an error on the JOIN statement.

I tried various kinds of JOIN statements, but I keep getting an error on the penultimate line. There is no problem if I eliminate x.name and the last two lines (e.g. the lines containing the JOIN statement and its subsequent line) - i.e. the code extracts the initial query listing the minimum Quant in each continent, but fails on the JOIN statement.

| ---------|------|-----|
| continent| name |Quant|
| ---------|------|-----|
| Asia     |  SL  | 140 |
| Asia     | Iran | 56  |      
| Asia     | Iraq | 43  |      
| Europe   | Italy| 60  |    
| Europe   | Spain| 38  |      
| Europe   | Eire | 12  |   
| Africa   | Kenya| 56  |
| Africa   | Zaire| 34  |
| Africa   | Egypt| 130 |
|----------|------|-----|   

SELECT y.continent, x.name, MIN(y.Quant) 
FROM golf y  
GROUP By y.continent  
LEFT JOIN golf x  
ON (y.continent = x.continent)

ALTERNATE variation below

SELECT y.continent, x.name, min(y.Quant) 
FROM golf y  
GROUP BY y.continent  
LEFT OUTER JOIN (select x.continent, x.name FROM golf x)  
ON (y.continent = x.continent)

The following is the message:

db error: ERROR: syntax error at or near "left"

Tushar
  • 3,527
  • 9
  • 27
  • 49
Abi
  • 13
  • 1
  • 2
    The GROUP BY goes to the very end of the query (_after_ the FROM and all JOINs) –  Mar 27 '23 at 07:33
  • 1
    The syntax of your SELECT statement should be `SELECT [columns] FROM [tables] GROUP BY [groupcolumns]`; Notably, `golf y LEFT JOIN golf x ON y.continent = x.continent` should all be in the "[tables]" bit. – Amadan Mar 27 '23 at 07:35
  • Please before considering posting: Pin down code issues via [mre]. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] Reflect research in posts. – philipxy Mar 27 '23 at 07:47
  • [Fetch the row which has the Max value for a column](https://stackoverflow.com/q/121387/3404097) – philipxy Mar 27 '23 at 08:09
  • @ only works for commenters on the post it's under. I saw your comment here. I also happened to see the copy you put under the answer, but that @ had no effect there. Thanks for telling me you found a solution to your overall goal, but comments are for asking for clarification or pointing out problems, so having read that comment I flagged it as obsolete & so a moderator deleted it. When your question is not closed (answering blocked) you can post your own answer. Although your comment answered your overall goal & not this question post. PS There is no question in this question post. – philipxy Mar 27 '23 at 10:27
  • Now you can flag my last comment & this one as 'no longer needed'. (First you can also upvote these as a kind of hopeful but unimportant signal that you saw it that I might see in case I happen to notice before it's gone, like I did to yours.) – philipxy Mar 27 '23 at 10:29
  • 1
    Look carefully at the documentation for [select](https://www.postgresql.org/docs/current/sql-select.html) statement. You will see that `join` along with the *join_type* (left, right, etc) is actually part of the `from` clause. – Belayer Mar 27 '23 at 21:48

1 Answers1

1

I think the issue is that LEFT JOIN should be positioned before GROUP BY. Try:

SELECT y.continent, x.name, MIN(y.Quant) 
FROM golf y  
LEFT JOIN golf x  
ON (y.continent = x.continent)  
GROUP BY y.continent, x.name
HAVING MIN(y.Quant) = y.Quant

Changed the order and added a HAVING clause to filter the rows to only those with the minimum Quant on each continent

Ps.to achieve this, we need to group by both y.continent and x.name

Also, note that if there are multiple countries with the same minimum Quant on a continent, this query will return all of them. If you only want one result per continent, you can modify the query to use a subquery to get the minimum Quant for each continent and then join that back to the golf table

Lemonina
  • 712
  • 2
  • 14
  • This is the new error message when I run your code: db error: ERROR: column "y.quant" must appear in the GROUP BY clause or be used in an aggregate function. On doing so, I get a result that is very different from what I wanted. Please explain why my sequence does not work? I extract the min Quant from each continent and the name of the continent first and then try and join the results of this query to another query that has the names of the countries as well as the continents. – Abi Mar 27 '23 at 08:17
  • I think the error means that the query cannot determine which value of y.Quant to use for each group of y.continent, x.name. maybe because y.Quant is not included in the GROUP BY clause and is not being used in an aggregate function -- Regarding your sequence, it does not work because you are selecting the minimum Quant and the continent separately, and then trying to join that result back to the golf table to get the name of the country. This doesn't work because the minimum Quant and the name of the country are not in the same row, so you cannot join them based on the continent alone. – Lemonina Mar 28 '23 at 05:02