0

I have a table with the following data:

Column A: Country Page

Column B: Link to post

Column C: Date posted

I created the following query to get the last date each Country Page posted:

=QUERY('Post Level'!A:C, "select A, max(C) where A<>'#N/A' and A<>'' group by A label max(C) 'Last Posted Date'",1)

The query works perfectly. I'd like to add also a link to the last post. So I changed it to:

=QUERY('Post Level'!A:C, "select A,B max(C) where A<>'#N/A' and A<>'' group by A,B label max(C) 'Last Posted Date'",1)

This version doesn't work. I get duplicate entries for each country.

Ideally I would like to see: Country Page, Last Date Country Posted, Link to last post

Matt M
  • 309
  • 4
  • 16
  • 1
    IMHO a really tough question. If I were faced with doing this in SQL I would find the last posted dates for each country like you did, then realise that the only way to get the corresponding links would be to link back to the original data using an inner join on Country page = country from my query and date = last date from my query. Unfortunately GS doesn't have an inner join although you can simulate it https://stackoverflow.com/questions/14796620/google-spreadsheet-query-join-equivalent-function?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa ...tricky. – Tom Sharpe Apr 10 '18 at 11:51
  • 2
    BTW would it be possible to show us a few lines of data plz? No promises that it will yield an answer, but would aid thought processes. – Tom Sharpe Apr 10 '18 at 12:16

1 Answers1

1

Well here is a proof of concept that it is do-able:

=ArrayFormula(iferror(vlookup({unique(A:A)&query(A:C, "select max(C) group by A offset 1 label max(C) 'Date'")},{A:A&C:C,B:B},2,false),""))

So I am concatenating the Country and Last Date and using them as a lookup value to get the associated Link.

The reason for using Unique rather than a query to get the list of countries is that you can't just put

select A group by A

You have to have at least one aggregate. As long as unique returns the countries in the same order as the group by (which it should do), this will work correctly.

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Hey Tom, first off, thanks for the attempt! I solved it like this (see after). Will test your versio and feedback back to you! Thanks for attempting: `=ArrayFormula(VLOOKUP(UNIQUE(FILTER('Post Level'!W2:W,'Post Level'!W2:W<>"")),QUERY(SORT('Post Level'!A2:X,3,false), "select Col23, Col3, Col8"),{2,1,3},0))` – Matt M Apr 11 '18 at 09:42