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.

CodePudding user response：

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)

CodePudding user response：

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

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

CodePudding user response：

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))}