Keeping only maximum date rows in a group The following SQL code keeps only the MAX(date) rows with the same id and question values. I would like to know if there is a simpler/ shorter syntax returning the same result.
with 
tbl_src as (select * from `tests2.o1.mc` order by id, date),
tbl_max_date as (
  select
    id,
    question,
    MAX(date) as max_date
  from
    `tests2.o1.mc`
  group by
    id,
    question
)
select 
  tbl_src.*
from
  tbl_src
inner join
  tbl_max_date
on
  tbl_src.id  = tbl_max_date.id
  and tbl_src.question  = tbl_max_date.question
  and tbl_src.date  = tbl_max_date.max_date

The original data:




id
date
question
answers




1
2018-03-21
q1
&quot;[&quot;&quot;n1&quot;&quot;,&quot;&quot;n3&quot;&quot;]&quot;


1
2018-12-10
q1
&quot;[&quot;&quot;n1&quot;&quot;,&quot;&quot;n2&quot;&quot;,&quot;&quot;n3&quot;&quot;]&quot;


1
2018-03-21
q2
&quot;[&quot;&quot;N1&quot;&quot;,&quot;&quot;n3&quot;&quot;]&quot;


1
2018-12-10
q2
&quot;[&quot;&quot;n1&quot;&quot;,&quot;&quot;n3&quot;&quot;]&quot;


1
2018-03-21
q3
&quot;[&quot;&quot;N1&quot;&quot;]&quot;


1
2018-12-10
q3
&quot;[&quot;&quot;n2&quot;&quot;]&quot;


2
2018-03-29
q1
&quot;[&quot;&quot;n1&quot;&quot;,&quot;&quot;n3&quot;&quot;]&quot;


2
2018-06-01
q1
&quot;[&quot;&quot;n1&quot;&quot;,&quot;&quot;n2&quot;&quot;,&quot;&quot;n3&quot;&quot;]&quot;


2
2018-06-02
q1
&quot;[&quot;&quot;n1&quot;&quot;,&quot;&quot;n3&quot;&quot;]&quot;


2
2018-06-01
q2
&quot;[&quot;&quot;n1&quot;&quot;,&quot;&quot;N2&quot;&quot;]&quot;


2
2018-06-01
q3
&quot;[&quot;&quot;n3&quot;&quot;]&quot;


3
2018-03-14
q1
&quot;[&quot;&quot;n2&quot;&quot;,&quot;&quot;n3&quot;&quot;]&quot;


3
2018-03-26
q2
&quot;[&quot;&quot;n1&quot;&quot;]&quot;


3
2018-03-14
q3
&quot;[&quot;&quot;n3&quot;&quot;]&quot;




The result:




id
date
question
answers




1
2018-12-10
q1
&quot;[&quot;&quot;n1&quot;&quot;,&quot;&quot;n2&quot;&quot;,&quot;&quot;n3&quot;&quot;]&quot;


1
2018-12-10
q2
&quot;[&quot;&quot;n1&quot;&quot;,&quot;&quot;n3&quot;&quot;]&quot;


1
2018-12-10
q3
&quot;[&quot;&quot;n2&quot;&quot;]&quot;


2
2018-06-02
q1
&quot;[&quot;&quot;n1&quot;&quot;,&quot;&quot;n3&quot;&quot;]&quot;


2
2018-06-01
q2
&quot;[&quot;&quot;n1&quot;&quot;,&quot;&quot;N2&quot;&quot;]&quot;


2
2018-06-01
q3
&quot;[&quot;&quot;n3&quot;&quot;]&quot;


3
2018-03-14
q1
&quot;[&quot;&quot;n2&quot;&quot;,&quot;&quot;n3&quot;&quot;]&quot;


3
2018-03-26
q2
&quot;[&quot;&quot;n1&quot;&quot;]&quot;


3
2018-03-14
q3
&quot;[&quot;&quot;n3&quot;&quot;]&quot;
You can use ROW_NUMBER to rank your data according to date for each combination of id and question; then simply select the row with a ROW_NUMBER of 1:
WITH tbl_max_date AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY id, question ORDER BY date DESC) AS rn
  FROM tests2.o1.mc
)
SELECT *
FROM tbl_max_date
WHERE rn = 1

If you could have more than one row with the same maximum value per group, you can use RANK in place of ROW_NUMBER, as that will give all rows with the same value the same ranking. For example:
WITH tbl_max_date AS (
  SELECT *,
         RANK() OVER (PARTITION BY id, question ORDER BY date DESC) aS rn
  FROM tbl_src
)
SELECT *
FROM tbl_max_date
WHERE rn = 1