QUERY Function that pulls latest date from a group of data

Question

I have this data from a sheet1:

colA -- colB -- colC -- colD -- colE
user1 -- prodA -- 1mo -- 2020-05-01 -- 50
user1 -- prodA -- 1mo -- 2020-06-01 -- 50
user1 -- prodA -- 1mo -- 2020-07-01 -- 50
user1 -- prodA -- 1mo -- 2020-08-01 -- 50
user1 -- prodA -- 1mo -- 2020-09-01 -- 45

Then I need to just show up in sheet2 col A,B,C and the latest date from colD and the value of that row from colE. How can I pull that using QUERY?

Output:

user1 -- prodA -- 1mo -- 2020-09-01 -- 45

Thanks

Answer 1

You can do it like this:

=QUERY(Sheet1!A:E,"select A,B,C,D,E order by D desc limit 1")

Order D by descending and limit 1.

To remove the headers, you can do this:

=INDEX(QUERY(Sheet1!A:E,"select A,B,C,D,E order by D desc limit 1"),2)

Or as a pure QUERY:

=QUERY(Sheet1!A:E,"select A,B,C,D,E 
order by D desc 
limit 1
label A'',B'',C'',D'',E''")