topic Re: Query Builder - Unique Records in Discussions

Query Builder - Unique Records

altug_bayram — Mon, 11 Apr 2016 20:58:55 GMT

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

Re: Query Builder - Unique Records

Bill_Worley — Wed, 19 Oct 2016 02:23:20 GMT

Hi Altug,

Please see the image below for getting a descending order for time.

Right click on your time variable in Available Columns and select "Order By" a new dialogue box will pop up in the lower right. From there you can pick the descending arrow to get the desired time order.

You could then use a filter with a prompt to select a unique asset level.

Re: Query Builder - Unique Records

altug_bayram — Tue, 12 Apr 2016 13:36:52 GMT

Bill

Thanks for quick reply.

However, unless I am missing a critical thing here, this does not solve my problem.

1) Any asset's last valid point are typically not at the same time point, they are quite different. They can have years of difference.

2) I am not looking for a prompt solution if possible. I need this to be fully automated.

3) I am really not sure how "Filter By" with >= can make it unique either. I think you rather meant to filter by "="

So I need an approach that picks up the latest chronological entry for any asset without having to specify a time filter (cause the latest data can be anywhere in time).

Thanks.

Re: Query Builder - Unique Records

Eric_Hill — Wed, 19 Oct 2016 02:23:35 GMT

Hey, altug,

Here is a table that I believe resembles what you are describing (but assume it is in a database rather than JMP):

My understand is that you would like to retrieve one row for A1, A2, A3, and A4 for which Time is the maximum. Is that correct?

I do not believe that is possible without doing a subquery that uses the MAX function, which you can do using a Custom Expression filter. Here is the query I ended up with:

In case that is too small to read, the custom expression is:

t1.Time = (select MAX(Time) from parts t2 where t1.Asset = t2.Asset)

So you pass in the value of Asset from the main query (t1.Asset) and select rows that have the maximum Time value for that asset.

Let me know if that helps.

Eric

Re: Query Builder - Unique Records

altug_bayram — Tue, 12 Apr 2016 14:48:11 GMT

Hello Eric,

First thanks for your help ....

Your understanding is correct. I am just trying to pull a single row for each asset where Time is max (or most recent).

I applied your formulae. However, it is giving me an error.

I applied it as the real table name first and then as "t1"

t1.calendar_month = (select MAX(calendar_month) from my_table t2 where t1.engine_serial_num = t2.engine_serial_num)

t1.calendar_month = (select MAX(calendar_month) from t1 t2 where t1.engine_serial_num = t2.engine_serial_num)

ERROR : relation "my_table" does not exist.

ERROR while executing the query [ SQLSTATE: 42P01 ]

Re: Query Builder - Unique Records

Eric_Hill — Tue, 12 Apr 2016 14:53:57 GMT

Hey, altug,

What database are you using?

Would you be willing to share the SQL from your Custom SQL query that gives you the result you are looking for?

Thanks,

Eric

Re: Query Builder - Unique Records

Eric_Hill — Tue, 12 Apr 2016 16:47:39 GMT

My original example was for SQL Server, which is a little more forgiving. For Oracle, I had to format the table name in the subquery as schema.table, plus I had to quote my mixed case names:

t1."Time" = (select MAX(t2."Time") from SQBTEST."parts" t2 where t2."Asset" = t1."Asset")

Try changing my_table in your first attempt to <schema>.my_table and see if that helps.

Eric

Re: Query Builder - Unique Records

altug_bayram — Tue, 12 Apr 2016 17:59:31 GMT

Eric,

Thanks a lot ....

Sorry, I took some time to figure it out w/ another query skilled person at GE.

Your solution (first one) definitely worked !!! I haven't tried the second one yet.

I do have one more issue -

it turns out folks who populate the database inserted duplicate entry for a few assets at the same last chronological time.. So to get around it, I have another column titled say X . Manually I would simply choose the two w/ the largest X. I did not want to convert from Max(Time) to Max(X) since X by itself may not be that reliable but would be the selection factor for duplicate Time entries.

We tried converting your original custom expression to sort of including a second MAX (on X) ... but that did not work...

So .. trying to pull each Asset where Time is MAX and if multiple entries selecting the entry w/ Max(X)

Any suggestions

Re: Query Builder - Unique Records

altug_bayram — Tue, 12 Apr 2016 18:00:48 GMT

I forgot to add ...

The reason I couldn't make your query work originally is that I was not referring to the database in the correct manner. Once we did that, it worked

Re: Query Builder - Unique Records

pmroz — Tue, 12 Apr 2016 18:28:47 GMT

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

Re: Query Builder - Unique Records

Eric_Hill — Wed, 19 Oct 2016 02:24:00 GMT

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)

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

Re: Query Builder - Unique Records

altug_bayram — Tue, 12 Apr 2016 19:17:50 GMT

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

Re: Query Builder - Unique Records

Eric_Hill — Wed, 19 Oct 2016 02:24:15 GMT

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.

Eric

Re: Query Builder - Unique Records

altug_bayram — Tue, 12 Apr 2016 19:37:59 GMT

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.

Re: Query Builder - Unique Records

Eric_Hill — Wed, 13 Apr 2016 01:20:38 GMT

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

Take care,

Eric

Re: Query Builder - Unique Records

JPKO — Wed, 23 Oct 2019 21:19:56 GMT

This is an old post, but here goes:

@altug_bayram wrote:
I forgot to add ...
The reason I couldn't make your query work originally is that I was not referring to the database in the correct manner. Once we did that, it worked

What was the correct manner? I also get an error that says "ERROR: relation "databases_enzyme_db" does not exist; Error while preparing parameters [SQLSTATE=42P01]" and I am unsure how to trouble shoot it