How to Select Records with Maximum Values in Two Columns

Select records using max values for two columns

Quick answer, use NOT EXISTS to verify the same id has no other row with a later year or same year but later month:

select v1.*
from VENDORMONTHLY v1
where not exists (select 1 from VENDORMONTHLY v2
                  where v2.Vendor = v1.Vendor
                    and (v2.Year > v1.year
                      or (v2.Year = v1.Year and v2.Month > v1.Month)))

Will return both rows in case of a latest row tie.

Core ANSI SQL-99. Will run on any dbms!

How to select records with maximum values in two columns?

Analytic functions are your friend:

SELECT   MAX( year    ) KEEP ( DENSE_RANK LAST ORDER BY year ASC, quarter ASC, message ASC ) AS year,
         MAX( quarter ) KEEP ( DENSE_RANK LAST ORDER BY year ASC, quarter ASC, message ASC ) AS quarter,
         MAX( message ) KEEP ( DENSE_RANK LAST ORDER BY year ASC, quarter ASC, message ASC ) AS message,
         type
FROM     info
GROUP BY type;

SQLFIDDLE

Select rows with Max Value grouped by two columns

Try this:

select t1.* from attachments t1
left join attachments t2
on t1.entity_id = t2.entity_id and t1.group_id = t2.group_id and
   t1.version_number < t2.version_number
where t2.version_number is null

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

Select the row with the maximum value in each group based on multiple columns in R dplyr

We may get rowwise max of the 'count' columns with pmax, grouped by 'col1', filter the rows where the max value of 'Max' column is.

library(dplyr)
df1 %>% 
 mutate(Max = pmax(count_col1, count_col2) ) %>%
 group_by(col1) %>%
 filter(Max == max(Max)) %>%
 ungroup %>%
 select(-Max)

-output

# A tibble: 3 × 4
  col1   col2   count_col1 count_col2
  <chr>  <chr>       <dbl>      <dbl>
1 apple  aple            1          4
2 banana banan           4          1
3 banana bananb          4          1

---

We may also use slice_max

library(purrr)
df1 %>%
  group_by(col1) %>%
  slice_max(invoke(pmax, across(starts_with("count")))) %>%
  ungroup
# A tibble: 3 × 4
  col1   col2   count_col1 count_col2
  <chr>  <chr>       <dbl>      <dbl>
1 apple  aple            1          4
2 banana banan           4          1
3 banana bananb          4          1

Select rows with Max(Column Value) for each unique combination of two other columns

In MySQL 5.x you can use a sub-query.

SELECT * 
FROM your_table
WHERE (`Group`, Dataset, RunNumber) IN (
    SELECT `Group`, Dataset, MAX(RunNumber) AS MaxRunNumber
    FROM your_table
    GROUP BY `Group`, Dataset
  );

Test on db<>fiddle here

Alternatives

--
-- LEFT JOIN on bigger
--
SELECT t.* 
FROM your_table t
LEFT JOIN your_table t2
  ON t2.`Group` = t.`Group`
 AND t2.Dataset = t.Dataset
 AND t2.RunNumber > t.RunNumber
WHERE t2.RunNumber IS NULL
ORDER BY t.`Group`, t.Dataset;

--
-- where NOT EXISTS on bigger
--
SELECT * 
FROM your_table t
WHERE NOT EXISTS (
  SELECT 1
  FROM your_table t2
  WHERE t2.`Group` = t.`Group`
    AND t2.Dataset = t.Dataset
    AND t2.RunNumber > t.RunNumber
)
ORDER BY `Group`, Dataset;

--
-- Emulating DENSE_RANK = 1 with variables
-- Works also in 5.x
--
SELECT RunNumber, `Group`, Dataset, Total
FROM
(
  SELECT 
    @rnk:=IF(@ds=Dataset AND @grp=`Group`, IF(@run=RunNumber, @rnk, @rnk+1), 1) AS Rnk
  , @grp := `Group` as `Group`
  , @ds := Dataset as Dataset
  , @run := RunNumber as RunNumber
  , Total
  FROM your_table t
  CROSS JOIN (SELECT @grp:=null, @ds:=null, @run:=null, @rnk := 0) var
  ORDER BY `Group`, Dataset, RunNumber DESC
) q
WHERE Rnk = 1
ORDER BY `Group`, Dataset;

--
-- DENSE_RANK = 1
-- MySql 8 and beyond. 
--
SELECT *
FROM
(
  SELECT *
  , DENSE_RANK() OVER (PARTITION BY `Group`, Dataset ORDER BY RunNumber DESC) AS rnk
  FROM your_table
) q
WHERE rnk = 1
ORDER BY `Group`, Dataset;

How to select rows based on two columns creating an identifier and the max date

Use window functions MAX() and FIRST_VALUE() to get the values of PostingDate and Quantity respectively of the row with the latest PostingDate:

SELECT DISTINCT
       ItemNo + ' ' + Lot AS Identifier,
       MAX(PostingDate) OVER (PARTITION BY ItemNo, Lot, Description) AS PostingDate,
       ItemNo,
       Description,
       FIRST_VALUE(Quantity) OVER (PARTITION BY ItemNo, Lot, Description ORDER BY PostingDate DESC) AS Quantity,
       Lot
FROM dbo.JournalLine

How to retrieve the values of the table for the max value of two columns in sql

Use analytic rank() function. rank() will assign 1 to all records with max date in the st_id partition:

SELECT s.st_id, s.fl_vw, s.tr_record
from
(
SELECT A.st_id, A.fl_vw, A.tr_record,
       rank() over(partition by st_id order by as_of_dt desc) rnk_as_of_dt,
       rank() over(partition by st_id order by ld_dt_ts desc) rnk_ld_dt_tsrnk
  FROM db.tablename A 
)s 
WHERE rnk_as_of_dt=1 ANDrnk=1 rnk_ld_dt_ts=1 --get records with max dates in both columns

Two ranks can be possibly combined like this:

SELECT s.st_id, s.fl_vw, s.tr_record
from
(
SELECT A.st_id, A.fl_vw, A.tr_record,
       rank() over(partition by st_id order by as_of_dt desc, ld_dt_ts desc) rnk
  FROM db.tablename A 
)s 
WHERE rnk=1  --get records with max dates combination 

but this is not exactly the same as your original query.
For example if you have this dataset:

st_id, as_of_dt, ld_dt_ts 
1       1         2
1       2         1

Then this query

SELECT st_id, max(as_of_dt) AS as_of_dt, max(ld_dt_ts) AS ld_dt_ts 
    From db.tablename 
    group by st_id

will return:

st_id, as_of_dt, ld_dt_ts 
1       2         2

And final join will not return any rows, because no any row with such combination exist, while query with two ranks combined will return:

st_id, as_of_dt, ld_dt_ts 
1       2         1

If such dataset can not exist in your data (say, ld_dt_ts is always>=as_of_dt), then you possibly can combine ranks into single one or even use only one date in the order by.

Select rows with maximum value where values in two columns are same

If you want the maximum, you can use window functions:

select hotelNo, roomType
from (select t.*, row_number() over (partition by hotelNo order by totalBooking desc) as seqnum
      from table t
     ) t
where seqnum = 1;

---

Related Topics