DISTINCT won't help the OP, because there really aren't any distinct rows here.
I found a query that would solve the second case. I added 6 rows at the bottom that duplicate the max times for each asset, plus added an X variable. Here is the table:
And here is the query that works (it requires one subquery and one subquery within a subquery)):
The custom expression is:
t1.Time = (SELECT MAX(tInner.Time) FROM parts tInner WHERE t1.Asset = tInner.Asset)
t1.X = (SELECT MAX(tInner.X) FROM parts tInner WHERE t1.Asset = tInner.Asset AND
tInner.Time = (SELECT MAX(tInner2.Time) FROM parts tInner2 WHERE tInner.Asset = tInner2.Asset))
I capitalized all the SQL keywords to make it easier to read. I tried some simpler queries, but they did not work.
It worked like a charm ...
I am wondering though ... would it have also worked by running the first original version of custom epxression (with only Time) AND while choosing X aggregation as "Maximum" inside "Included Columns" field ... ?
thanks so much ....
Running the original version with only Time and using the MAX aggregation on X can be made to return the correct rows, but you will not be able to find out the values of p1 and p2 for those rows. If I do what you said leaving p1 and p2 in the query I get all the rows with the duplicate max times, because p1 and p2 become GROUP BY's for the query:
Now, if I take p1 and p2 out of the query, the query returns the correct rows:
but the whole point was to find the values of p1 and p2 for those rows, and they are gone. So I think the double subquery approach is the only approach that answers the question you asked.
I understand better now and agree w/ you . Again , thanks for the effort you provided to this issue.
Your help has been very valuable.
My pleasure. This thread really pushed my understanding of subqueries. I learned a lot.