HOW to delete the same data in the table？dear，
every time i go to menu which “row” to detele the same data ，
i want to delete the same data in the table automaticly， and i don t know to how to write JSL
how i do to write ？
Re: HOW to detele the same data in the table？I just started from scratch and it worked on my end. Tried both MIn() and Max() and it gave what I was expecting.
I did notice a couple of things:
1. When I told you I added column 1, I forgot to mentioned I initialized it to sequential numeric. IE I basically replicated the row number.
2. Noticed you use the brackets around the table name, and the table name ends in "$". Those are MS take on SQL. Try eliminating those.
3. Can't remember if I told you to hit the update button in the custom sql window or not, but seems to help me before I run query.
Re: HOW to detele the same data in the table？
i am sorry to tell you that it can not work
the alarm is circular reference;
SELECT t1.*
FROM [Solubility$] t1,
( SELECT DISTINCT "LOT NO", Min("Column 1") as "Column 1" FROM [Solubility$] GROUP BY "LOT NO" ) AS t2
WHERE t1."Column 1" = t2."Column 1"
Re: HOW to detele the same data in the table？I went ahead and redid it by putting subquery as table so you can see difference. Sometimes one way works better or is clearer than the other. Here the "t1" and "t2" are aliases.
SELECT t1.*
FROM Solubility t1,
( SELECT DISTINCT "LOT NO", Min("Column 1") AS "Column 1" FROM Solubility GROUP BY "LOT NO" ) AS t2
WHERE t1."Column 1" = t2."Column 1"
;
Re: HOW to detele the same data in the table？Made slight mistake - using Min will return your "delete" table.Re: HOW to detele the same data in the table？Your problem is that you are using DISTINCT across all columns of solubility. Because for LOT NO 1700002, you have unique values (ie 'elec break', 'pin hole') in column abnormal content, you keep getting the same rows back because each row is unique on the set of columns.
If I remember correctly, Excel (ie M$) sql has a FIRST or LAST version, SAS (ie JMP) does not (although that might have changed under current versions.
Here's code I wrote that seems to answer your question:
SELECT t2.*
FROM Solubility t2
WHERE t2."Column 1" IN (
SELECT DISTINCT t1."Column 1"
FROM Solubility t1
GROUP BY t1."LOT NO"
HAVING t1."Column 1" = Max(t1."Column 1"));
I opened Solubility into JMP
I added a column, Column 1 (think of this as a Sort/Order column)
Next I went into SQL Query builder under tables
After Picking Solublity as table, I picked "column 1", and "lot no", which I'm going to use in a coorelated sub-query (ie a temporary table), and checked DISTINCT Rows only, and clicked add
Now I clicked the triangle next to query Name and chose Convert to Sql. This will let me write the code (30 years of SAS - click and point only get you so far).
I editted the code to what I gave you above, ran the query, corrected any errors that came up, until it ran right.
Basically I matched your modified solubility on column 1 on the correlated subquery mentioned above. The subquery returns unique "column 1"s by taking the maximum column 1 of each group of "lot no". I'm using Max() here as a subsitute for last. Min() could be used as a substitute for FIRST.
I could have also done this as a JOIN on the subquery as another table. We'll leave that for tomorrow.
I know this is probably pretty esoteric, but if you have any questions let me know, Ill try to answer them.
RCW
Re: HOW to detele the same data in the table？thanks
attached file with excel "Solubility" is original data which i want to modify
attached file"data.dsn" is database
the result which i want to be is atteched excel named "delete"
I write “ SELECT distinct [LOT NO],code,[abnormal content] FROM [Solubility$]”
to delete the same data which in "LOT NO"
but it can not worked .
Re: HOW to detele the same data in the table？The JMP Query Builder lets you add JSL to the query. Look at the documentation on Page 121 in
Help==>Books==>Using JMP
It will allow you to add to your query the code to automatically delete the desired rows, every time you extract the data.
Re: HOW to detele the same data in the table？thanks
I use ”Built Query“of JMP menu to get data from database，
@txnelson wrote:
When you open the data from the database, you will have to add the JSL to the script that you use for retrieving the data. What method are you using for getting the data from the database?
Re: HOW to detele the same data in the table？When you open the data from the database, you will have to add the JSL to the script that you use for retrieving the data. What method are you using for getting the data from the database?
Re: HOW to detele the same data in the table？thanks，
the data table comes from database，and everytime i open it， the data is refreshed 。
so i expect the JSL to be automatically re-executed to delete the same data appear in the table。
@txnelson wrote:
Please clarify. I am confused. In the data table you attached, you have 2 columns, "first" and "second". If you want to delete the rows based upon finding the value of "A" in the column named "first" the JSL would be:
dt = Current Data Table();
dt << Select Where( :first == "A") << Delete Rows;
If you wanted to delete rows based upon the value of "A" being in the column named "second", the JSL would be:
dt = Current Data Table();
dt << Select Where( :second == "A") << Delete Rows;
So when you say "but if i refresh data "........are you indicating, that when you reopen the data table? Are you expecting the JSL to be automatically re-executed when you open the data table again?
data when i open the data table again。
Re: HOW to detele the same data in the table？Please clarify. I am confused. In the data table you attached, you have 2 columns, "first" and "second". If you want to delete the rows based upon finding the value of "A" in the column named "first" the JSL would be:
dt = Current Data Table();
dt << Select Where( :first == "A") << Delete Rows;
If you wanted to delete rows based upon the value of "A" being in the column named "second", the JSL would be:
dt = Current Data Table();
dt << Select Where( :second == "A") << Delete Rows;
So when you say "but if i refresh data "........are you indicating, that when you reopen the data table? Are you expecting the JSL to be automatically re-executed when you open the data table again?
Re: HOW to detele the same data in the table？thanks，
but if i refresh data ， i can not delete the same data automatic which below to
dt = Current Data Table();
dt << Select Where( :Col1 == "A") << Delete Rows;
Re: HOW to detele the same data in the table？Assuming there is some condition you can write to select the data you want to delete, check out Select Where() in the scripting index.
e.g.:
dt = Current Data Table();
dt << Select Where( :Col1 == "A") << Delete Rows;
This would delete any rows in Col1 with the value "A".
