BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
Eric_Hill

Staff

Joined:

Oct 1, 2013

Solution

Re: Query Builder - Unique Records

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:

11335_parts1_duptimes.png

And here is the query that works (it requires one subquery and one subquery within a subquery)):

11336_parts2_duptimes_query.png

The custom expression is:

t1.Time = (SELECT MAX(tInner.Time) FROM parts tInner WHERE t1.Asset = tInner.Asset)

AND

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.

HTH,

Eric

altug_bayram

Community Trekker

Joined:

Apr 11, 2016

Re: Query Builder - Unique Records

Eric,

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 ....

Eric_Hill

Staff

Joined:

Oct 1, 2013

Re: Query Builder - Unique Records

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:

11337_query_max_x.png

Now, if I take p1 and p2 out of the query, the query returns the correct rows:

11338_query_max_x_no_ps.png

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.

Eric

altug_bayram

Community Trekker

Joined:

Apr 11, 2016

Re: Query Builder - Unique Records

Eric,

I understand better now and agree w/ you . Again , thanks for the effort you provided to this issue.

Your help has been very valuable.

Eric_Hill

Staff

Joined:

Oct 1, 2013

Re: Query Builder - Unique Records

My pleasure.  This thread really pushed my understanding of subqueries.  I learned a lot.

Take care,

Eric