group by and select row with the max date, and select all columns

Question

I have the following data in a sheet.

event_id	event_type	event_name	date_col
123	critical_event	Event A	2021/12/16
456	critical_event	Event B	2021/12/25
999	medium_event	Event C	2021/12/13
888	medium_event	Event D	2021/12/16

I'm using the following query in another tab which would give me the latest event for each event_type (by using max(date_col)).

=QUERY(data!A:C, "select B, max(D) group by B")

However the query only returns the data for 2 columns.

event_type	max date_col
critical_event	2021/12/25
medium_event	2021/12/16

while what I want is to include the event_id and event_name columns as well. (Selecting that row which has the max date) Like below:

event_id	event_type	event_name	max date_col
456	critical_event	Event B	2021/12/25
888	medium_event	Event D	2021/12/16

If I select the event_id column in the query, the query breaks.

score 1 · Accepted Answer · answered Nov 20 '22 at 13:09

1

to not break query you would need to use:

=QUERY(data!A:C, "select A,B,max(C) group by A,B")

but try:

=SORTN(SORT(A1:C, 3, 0), 9^9, 2, 2, 1)

answered Nov 20 '22 at 13:09

player0

Thank you. But I don't want to group by multiple columns. Is there anyway I could get the other columns' values of that row which has the max date value? – impy Nov 20 '22 at 13:47
@Imran did you try 2nd formula? – player0 Nov 20 '22 at 22:32
2nd formula works! I think that, accordingly to what OP posted originally the range set was data!A1:C, but it should be `data!A2:D` . Could you try @Imran? – Martín Nov 21 '22 at 04:29
Sorry for the wait. I was understanding how the sortn and sort formula works.It's working. Thank you guys. Also, how fast is it going to be if we have like thousands of rows? – impy Nov 21 '22 at 08:42
1

@Imran sorting operations are ono of fastest one so even with 10K rows it should be ok – player0 Nov 21 '22 at 12:27

score 0 · Answer 2 · answered Nov 20 '22 at 13:10

0

Use the below query. All the required columns will be displayed.

=QUERY(data!A:C, "select A, B, max(C) group by A,B")

answered Nov 20 '22 at 13:10

nixxo_raa

Thanks, but can this be done without grouping by multiple columns? Not necessarily using queries. – impy Nov 20 '22 at 13:49

score 0 · Answer 3 · answered Nov 20 '22 at 14:40

0

If you are trying to return the rows where date_col has the max value, use:

={A1:C1;FILTER(A2:C5,C2:C5=MAX(C2:C5))}

answered Nov 20 '22 at 14:40

z''

3 Answers3