CODEWITHSUNDEEP
sqlmariadb
Rapid1898
Rapid1898

Reputation: 1220

MAX in Select statement not returning the highest value?

I have a question regarding the max-statement in a select -

Without the MAX-statemen i have this select:

SELECT stockID, DATE, close, symbol
  FROM ta_stockprice JOIN ta_stock ON ta_stock.id = ta_stockprice.stockID
WHERE stockid = 8648
ORDER BY close

enter image description here

At the end i only want to have the max row for the close-column so i tried:

enter image description here

Why i didn´t get date = "2021-07-02" as output?

(i saw that i allways get "2021-07-01" as output - no matter if i use MAX / MIN / AVG...)

Upvotes: 0

Views: 813

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

The MAX() turns the query into an aggregation query. With no GROUP BY, it returns one row. But the query is syntactically incorrect, because it mixes aggregated and unaggregated columns.

Once upon a time, MySQL allowed such syntax in violation of the SQL Standard but returned values from arbitrary rows for the unaggreged columns.

Use ORDER BY to do what you want:

SELECT stockID, DATE, close, symbol
FROM ta_stockprice JOIN ta_stock ON ta_stock.id = ta_stockprice.stockID
WHERE stockid = 8648
ORDER BY close DESC
LIMIT 1;

Upvotes: 3

Related Questions