How can I express this query in a way that will work on excel data?

Question

I do have this final query (I've tried a lot of them to make this work), which i'm not able to get it run.

 SELECT customerNo, rating, dateApproved
 FROM ['worksheet$']
 WHERE dateCreated = (SELECT MAX(dateCreated) FROM ['worksheet$'] )
 AND status = 'Approved'"

For a customerNo 0001 there are two or maybe more rows, and I want to get the row where the dateApproved is the maximum for that customerNo.

How can i achieve this? It's causing me a lot of headache. Already asked a few friends and also googled, no relevant answers found.

thanks in advance

Answer 1

The "SELECT MAX(..." subquery is going to return the max date created for the ENTIRE worksheet; not just for the specific customer you care about.

I'm not sure if Excel will support this syntax, but in a normal database I'd suggest this query to get what you want:

 SELECT customerNo, rating, dateApproved
 FROM ['worksheet$'] wkOuter
 WHERE status = 'Approved'
   and dateCreated = (
   SELECT MAX(dateCreated)
   FROM ['worksheet$'] wkInner
   WHERE wkInner.customerNo = wkOuter.customerNo
   )