CODEWITHSUNDEEP
mysqlsqlquery-optimizationaggregate-functions
ts.
ts.

Reputation: 10709

MAX on two columns in mysql

I wonder if there is better way to get max from column c1 and then max from column c2 for selected rows than 

SELECT MAX(c1) , MAX(c2) FROM t GROUP BY c1 HAVING c1 = MAX(c1)

Upvotes: 0

Views: 5745

Answers (4)

Sean
Sean

Reputation: 15144

Just read your comment on hkutluay's response, this might be what you want:

select MAX(colName)
from
(select col1 as colName
from tableName
union
select col2
from tableName) subqueryName

Upvotes: 1

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

SELECT Max(t2.c1) as C1, 
       Max(t1.c2) as C2 
FROM   t t1 
       INNER JOIN (SELECT Max(c1) AS C1 
                   FROM   t) t2 
               ON t1.c1 = t2.c1

Upvotes: 5

Martijn
Martijn

Reputation: 3754

I'm not quite sure whether you want all distinct c1's or just the maximum.

If you want all c1's:

SELECT c1, MAX(c2) FROM t GROUP BY c1;

If you want only the maximum c1:

SELECT c1, c2 FROM t ORDER BY c1 DESC, c2 DESC LIMIT 1;

Upvotes: 1

wildplasser
wildplasser

Reputation: 44250

I know mysql does not have CTE's; but because the question is also marked SQL, and the CTE is a reasonable clean solution for the subquery, for completeness here is a CTE-version:

WITH zmax AS (
        SELECT MAX(v1) AS v1
        FROM ztable
        )
SELECT zt.v1
        , MAX(zt.v2) AS v2
FROM ztable zt
JOIN zmax mx ON zt.v1 = mx.v1
GROUP BY zt.v1
        ;

Upvotes: 0

Related Questions