Member Avatar for rajhoq12

SELECT col2 FROM table WHERE col1=max(col1?);

  • 3 Contributors
  • 2 Replies
  • 117 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by rishif2

Dani AI

Generated

Short answer: there are two different problems to solve — get the maximum value itself, or get the row(s) that contain that maximum. correctly pointed out that aggregates can't be used directly in a WHERE clause; use an aggregate, a subquery/derived table, ORDER BY/LIMIT, or a window function depending on which result you want. was right to ask for clarification: pick the pattern below that matches your goal.

To return a single row that has the highest col1 (fast, and can use an index):

SELECT col2
FROM mytable
ORDER BY col1 DESC
LIMIT 1;

To return every row that ties for the maximum col1 (useful when duplicate max values must all be returned):

SELECT t.col2
FROM mytable t
JOIN (
  SELECT MAX(col1) AS mx
  FROM mytable
) m ON t.col1 = m.mx;

If using MySQL 8.0+, window functions give a compact way to include ties or pick a single ranked row:

SELECT col2
FROM (
  SELECT col2, RANK() OVER (ORDER BY col1 DESC) AS rnk
  FROM mytable
) x
WHERE rnk = 1;

For group-wise maxima (max per category), compute MAX(...) with GROUP BY in a derived table and join back to get full rows. Practical notes: MAX() ignores NULLs; if you want only one row from tied maxima add a tie-breaker (e.g., primary key) to ORDER BY; ORDER BY ... LIMIT 1 often uses an index and is very efficient on large tables — check with EXPLAIN; and use HAVING if you need to filter on aggregates after grouping.

Recommended Answers

All 2 Replies

Member Avatar for delta_frost

It is unclear what you wish to achieve by seeing the query you have posted.
To simply select max from a column/attribute
SELECT max(sal) FROM EMP //Displays maximum salary for the EMP table
It may be noted that group functions can't be used after the where clause
A second guess at what you are trying to achieve.
SELECT ename FROM emp WHERE sal = (SELECT max(sal) FROM emp) //Displays the ename having highest salary in the emp table.

Member Avatar for rishif2

please make your post more clear

and explain you want to do?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.