How to Use Max() on a Subquery Result

Select max value in subquery

i have created the test data fiddle and made an easy and understandable query to fetch the required data, i.e:

SELECT DISTINCT classId,
                std.name,
                Class.email,
                signUpDate
FROM CLASS
INNER JOIN Student std ON std.email = Class.email
WHERE signUpDate IN
    (SELECT max(signUpDate)
     FROM CLASS
     GROUP BY classId)

Sql Fiddle here

Select Max Data from Subquery SQL, but it Show All Results from Subquery

1st solution using where condition:

SELECT t1.valuesum, t1.codebook
FROM (
        SELECT SUM( s.value ) AS valuesum, s.codebook
        FROM stock s
        GROUP BY s.codebook
     ) t1
WHERE t1.valuesum in (SELECT MAX( t2.valuesum ) AS total
                     FROM (
                             SELECT SUM( s2.value ) AS valuesum, s2.codebook
                             FROM stock s2
                             GROUP BY s2.codebook
                           )t2
                     ) 

SQL Fiddle Demo

2nd solution using having condition:

SELECT max(t1.valuesum), t1.codebook
FROM (
        SELECT SUM( s.value ) AS valuesum, s.codebook
        FROM stock s
        GROUP BY s.codebook
     ) t1
GROUP BY t1.codebook
HAVING max(t1.valuesum) = (SELECT MAX( t2.valuesum ) AS total
                     FROM (
                             SELECT SUM( s2.value ) AS valuesum, s2.codebook
                             FROM stock s2
                             GROUP BY s2.codebook
                           )t2
                     )

SQL Fiddle Demo

SQL Select Max within a Subquery

you can use an alias for max column and your code will work :

SELECT m.nameFirst, m.nameLast, t.salary, te.name
FROM (
    SELECT MAX(salary) as salary, teamID 
    FROM salaries 
    GROUP BY teamID
) AS t, master AS m, teams AS te, salaries AS s
WHERE t.salary=s.salary AND s.teamID=t.teamID AND s.playerID = m.playerID AND te.teamID=t.teamID;

Select max value in subquery in SQL

that should work

select * from (select centre_name, sum(qty) as number1 from 
                (select exchange_from_centre_id as cenid, count(exchange_from_centre_id) as qty
                from as2.exchange
                group by exchange_from_centre_id
            union all
                select exchange_to_centre_id as cenid, count(exchange_to_centre_id) as qty
                from as2.exchange
                group by exchange_to_centre_id), as2.centre c
where c.centre_id = cenid
group by centre_name) where number1 = (select max(number1) from (select centre_name, sum(qty) as number1 from 
                (select exchange_from_centre_id as cenid, count(exchange_from_centre_id) as qty
                from as2.exchange
                group by exchange_from_centre_id
            union all
                select exchange_to_centre_id as cenid, count(exchange_to_centre_id) as qty
                from as2.exchange
                group by exchange_to_centre_id), as2.centre c
where c.centre_id = cenid
group by centre_name));

SQL query: Select max value of subselect

Here is a solution which avoids one of the correlated subqueries you had, instead replacing it with a LEFT JOIN:

SELECT a.*
FROM article a LEFT JOIN OrderItems o ON a.anr = o.artnr
WHERE o.artnr IS NULL AND
    a.price = (SELECT MAX(a.price)
               FROM article a LEFT JOIN OrderItems o ON a.anr = o.artnr
               WHERE o.artnr IS NULL)

This solution should be ANSI-92 compliant, meaning it should play friendly with MySQL, Oracle, SQL Server, and any other type of fast food you might encounter.

MySQL: Select MAX() from sub-query with COUNT()

Instead of MAX() you can simply use LIMIT for the same. Also use JOIN instead.

SELECT book_count,a.author_id,a.fn, a.ln 
FROM author a
JOIN
(
  SELECT c.author_id,COUNT(*) book_count FROM book c
  GROUP BY c.author_id
) b 
ON a.author_id = b.author_id
ORDER BY book_count DESC LIMIT 1

Output:

| BOOK_COUNT | AUTHOR_ID |    FN |     LN |
-------------------------------------------
|          2 |        12 | name1 | lname1 |

See this SQLFiddle

---

Edit:

If you want to use MAX() for that, you have to use sub-query like this:

SELECT book_count,a.author_id,a.fn, a.ln 
FROM author a
JOIN
(
  SELECT c.author_id,COUNT(*) book_count FROM book c
  GROUP BY c.author_id
) b 
ON a.author_id = b.author_id
WHERE book_count = 
        (SELECT MAX(book_count)
        FROM
        (
           SELECT c.author_id,COUNT(*) book_count FROM book c 
           GROUP BY c.author_id
        ) b )

See this SQLFiddle

---

Edit2:

Instead of using LIMIT in outer query you can simply use it in inner query too:

SELECT book_count,a.author_id,a.fn, a.ln 
FROM author a
JOIN
(
  SELECT c.author_id,COUNT(*) book_count FROM book c
  GROUP BY c.author_id
  ORDER BY COUNT(*) DESC LIMIT 1
) b 
ON a.author_id = b.author_id

See this SQLFiddle

---

Related Topics