CODEWITHSUNDEEP
sqlmax
user2140857
user2140857

Reputation: 139

How to return all rows with MAX value meeting a condition of another field in SQL?

I have the following costs table:

+--------+------+-----------+
|  Year  |  ID   |  Amount  |
+--------+------+-----------+
|  1960  |  1    |  100     |   
|  1960  |  2    |  200     | 
|  1960  |  3    |  200     |   
|  1960  |  4    |  150     |   
|  1961  |  1    |  300     |   
|  1961  |  2    |  200     |   
|  1961  |  3    |  100     |   
|  1961  |  4    |  300     |     
+---------+------+----------+

I want all ID’s having the MAX Amount by Year. For example, for 1960, I want rows with ID's 2 and 3. For 1961, I want rows with ID's 1 and 4.

SELECT Year, ID, Amount FROM costs WHERE Amount = (SELECT MAX(Amount) FROM costs);

The above gets me all MAX values across all Years. But I want a condition that only gets me the max Amount values per year. How do I add an condition to only select records with Year = 1960?

Upvotes: 1

Views: 1131

Answers (4)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522244

One option which should run on all major databases is to use a subquery which finds the max amounts for each year to select the records you want:

SELECT c1.*
FROM costs c1
INNER JOIN
(
    SELECT Year, MAX(Amount) AS MaxAmount
    FROM costs
    GROUP BY Year
) c2
    ON c1.Year   = c2.Year      AND
       c1.Amount = c2.MaxAmount

Another way to do this would be to use a correlated subquery:

SELECT c1.*
FROM costs c1
WHERE c1.Amount = (SELECT MAX(c2.Amount) FROM costs c2 WHERE c2.Year = c1.Year)

I expect that joining (the first option) would be the fastest method for larger tables, especially if you have proper indices would could be used.

Upvotes: 1

Rishabh Toki
Rishabh Toki

Reputation: 58

Try this....It should work

SELECT
    *
FROM
    costs
WHERE
    (YEAR, amount) IN (
        SELECT
            YEAR,
            max(amount)
        FROM
            costs
        GROUP BY
            YEAR
    );

Upvotes: 2

Faisal
Faisal

Reputation: 4765

Please try this with below query.This is tested. Its working fine.

By clicking on the below link you can see your expected result in live which you want.

SQL Fiddle Live Demo

SELECT
    t1.*
FROM
    costs t1
WHERE
    t1.amount = (
        SELECT
            MAX(t2.amount)
        FROM
            costs t2
        WHERE
            t2. `year` = t1. `year`
    );

Upvotes: 2

Mansoor
Mansoor

Reputation: 4192

SELECT Year , ID , Amount 
FROM #Table T1
JOIN
(
  SELECT MAX(Amount) Amount,Year
  FROM #Table
  GROUP BY Year
) A ON A.Year = T1.Year AND A.Amount = T1.Amount

Upvotes: 0

Related Questions