Selecting Multiple Max() Values Using a Single SQL Statement

Selecting multiple max() values using a single SQL statement

If you want to return the max value for each data_type in a separate column, then you should be able to use an aggregate function with a CASE expression:

select
  max(case when data_type='World of Warcraft' then value end) WorldofWarcraft,
  max(case when data_type='Quake 3' then value end) Quake3,
  max(case when data_type='Final Fantasy' then value end) FinalFantasy
from yourtable;

See SQL Fiddle with Demo

selecting multiple max values

You can use a derived table to get the maximum value and join it back to the original table to see all rows corresponding to it.

select t.id, t.item 
from tablename t
join (select max(item) as mxitem from tablename) x
on x.mxitem = t.item

Edit:

select t.co_travelers_id, t.booking_id, t.accounts_id 
from a_co_travelers t
join (select accounts_id, max(booking_id) as mxitem 
      from a_co_travelers
      group by accounts_id) x 
on x.mxitem = t.booking_id and t.accounts_id = x.accounts_id

Select multiple max values after GROUP BY query

A join based approach doesn't have this problem, and would retain all records tied for the max income on a given date.

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT date, MAX(income) AS max_income
    FROM yourTable
    GROUP BY date
) t2
    ON t1.date = t2.date AND t1.income = t2.max_income
ORDER BY
    t1.date;

The way the above query works is to join the complete original table to a subquery which finds, for each date, the maximum income value. This has the effect of filtering off any record which did not have the max income on a given date. Pay close attention to the join condition, which has two components, the date, and the income.

If your database supports analytic function, we can also use RANK here:

SELECT date, ID, income
FROM
(
    SELECT t.*, RANK() OVER (PARTITION BY date ORDER BY income DESC) rnk
    FROM yourTable t
) t
WHERE rnk = 1
ORDER BY date;

Multiple max values in a query

We can use multiply columns in an IN clause:

select id, bdate, value 
from myview 
where (id, bdate) in
    (select id, max(bdate)
     from myview group by id)
/

SQL select multiple max rows where ID is same

Query:

SQLFIDDLEExample

SELECT t1.*
FROM Table1 t1
WHERE t1.Date = (SELECT MAX(t2.Date)
                 FROM Table1 t2
                 WHERE t2.AccountID = t1.AccountID)

Result:

| ROW |                            DATE |    ACCOUNTID |
--------------------------------------------------------
|   1 | February, 21 2013 00:30:00+0000 | A0M8FA1003YP |
|   2 | February, 21 2013 00:30:00+0000 | A0M8FA1003YP |
|   3 | February, 21 2013 00:30:00+0000 | A0M8FA1003YP |
|   7 | February, 12 2009 12:00:00+0000 | A023123332YP |

SQL MAX of multiple columns?

This is an old answer and broken in many way.

See https://stackoverflow.com/a/6871572/194653 which has way more upvotes and works with sql server 2008+ and handles nulls, etc.

Original but problematic answer:

Well, you can use the CASE statement:

SELECT
    CASE
        WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
        WHEN Date2 >= Date1 AND Date2 >= Date3 THEN Date2
        WHEN Date3 >= Date1 AND Date3 >= Date2 THEN Date3
        ELSE                                        Date1
    END AS MostRecentDate

How to use MAX() for multiple occurrences of Max values in SQL

MAX() is a scalar function returns a single value and not records so if you have multiple records that have the same value which is maximum, the following will still return only one value:

SELECT MAX(Value) FROM MyTable

If you want to get all records that have the maximum value, you can use

SELECT * FROM MyTable
WHERE Value = (SELECT MAX(Value) FROM MyTable)

---

Related Topics