cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
altug_bayram
Level IV

Query Builder - Unique Records

Hello ,


After quite a bit of personal struggle to solve my problem, I've decided to post it as a question to user and JMP community.


Have a database from which p1, p2, asset and time are pulled from. Database is an accumulating database which means parameters p1, and p2 are loaded as representing the last state for each asset at a specific time. In other words, prior older instances of p1 and p2 are invalid, and the most recent versions of p1 and p2 are needed for a given asset at the latest reported time.


Hence, what  I need is an descending order via time and then force the outcome to be unique at asset level.

Any ideas of how I need to direct this in query builder ?


I rather prefer to have a solution by not using a custom SQL.... I actually have a solution at Custom SQL but I like to learn how to do this within query builder as I will be adding a few more pieces which be hard to do in the Custom SQL


Appreciate the help.

Altug Bayram

15 REPLIES 15
pmroz
Super User

Re: Query Builder - Unique Records

If it's a duplicate entry perhaps using the DISTINCT keyword will help.

Eric_Hill
Staff

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
Level IV

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

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
Level IV

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

Re: Query Builder - Unique Records

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

Take care,

Eric