SQL Learning MAX Question

[JC724]

I am trying to learn how to use the MAX function in the WHERE CLAUSE.

So say I had a table or 2 tables or 3. And I have


col1 col2 col3
A1 mark 200
A1 Jon 300
A2 Bill 100
A2 Jen 400
A3 will 250
A3 ben 700

And I want to return the MAX value for col1 in reference to col 3. SO

A1 300
A2 400
A3 700
What would be a good idea of how to do this? Should I even be using MAX?

 

[Snowknight26]

SELECT
co1, MAX(col3)
FROM table
GROUP BY col1

 

[Doward]

You should absolutely be using MAX That's the purpose of MAX after all, to get the largest value!

That said, if you've got these columns across multiple tables, you'll need to join the tables, perhaps something like:

SELECT a.col1, MAX(b.col3) FROM a INNER JOIN b on a.id=b.id GROUP BY a.col1

 

[StoleMyOwnCar]

There isn't really enough detail here. MAX is indeed used to get the largest value but what do you mean by "in reference to"? Like where is col1? Is it in the same table? Are you trying to get max values for another table based on its col1? Is there a foreign key constraint between them? What are your indices?