delete dupes with a unique id using a temp table

I need to delete the dupes across bu's.  The records are unique based
on bu+ndc but there are 400+ duplicates..
There is a sequential id that is unique.  I am trying to remove these
400+ records.

1.  SELECT *
FROM ci
GROUP BY bu,ndc
HAVING COUNT(concat(bu,ndc) )>1

This query yields 400+ dupes which I need to remove.


2.
I could do this query in Oracle but not in MySQL.
You cannot delete from the same table as in the subquery.


 DELETE FROM ci  b
WHERE EXISTS ((
SELECT bu,ndc, count(1)
FROM ci
GROUP BY bu,ndc
HAVING  COUNT(1)>1
) a
where  a.bu=b.bu
and a.ndc = b.ndc



3.   I need to create a temp table with the unique ids in order to
compare the ci table to the duplicates.
I create the temp table with the SELECT from above that now has the
duplicate records in it with the id.
CREATE TABLE  ci_tmp AS
(SELECT c.id,c.bu,c.zonenm,c.ndc
FROM ci c
GROUP BY c.bu,c.ndc
HAVING  COUNT(concat(c.bu,c.ndc) )>1)

4.  Next I have to run my delete query matching against the ids in the
temp table.
AT this point I am not sure exactly how to do this or what I need to
delete.
This is what I have but it isn't right because it doesn't delete any
records.


DELETE FROM cart_inventory
WHERE id IN (select id
FROM cart_inventory_tmp
GROUP BY bu,nationaldrugcode
HAVING COUNT(concat(bu,nationaldrugcode) )>1)


I also tried   deleting using max(id) but I don't know if it requires
a self-join and I think the above process might be easier?

delete  FROM cart_inventory as as a
max(id)
where  concat(a.bu, a.ndc)=concat(b.bu,b.ndc)

If you could explain the process of Query#4 what I am trying to match
against that might help but I basically understand
that you need to get the id of one of the duplicates from the temp
table and match it against the ci table and delete that id.
It seems like it should work but it doesn't because I run query # 4
and do the select for the dupes and they are all still there..



thanks,
0
jr
1/4/2011 12:37:05 AM
comp.databases.mysql 6886 articles. 8 followers. laredotornado (870) is leader. Post Follow

16 Replies
363 Views

Similar Articles

[PageSpeed] 27
On Jan 3, 7:37=A0pm, jr <jlro...@yahoo.com> wrote:
> I need to delete the dupes across bu's. =A0The records are unique based
> on bu+ndc but there are 400+ duplicates..
> There is a sequential id that is unique. =A0I am trying to remove these
> 400+ records.
>
> 1. =A0SELECT *
> FROM ci
> GROUP BY bu,ndc
> HAVING COUNT(concat(bu,ndc) )>1
>
> This query yields 400+ dupes which I need to remove.
>
> 2.
> I could do this query in Oracle but not in MySQL.
> You cannot delete from the same table as in the subquery.
>
> =A0DELETE FROM ci =A0b
> WHERE EXISTS ((
> SELECT bu,ndc, count(1)
> FROM ci
> GROUP BY bu,ndc
> HAVING =A0COUNT(1)>1
> ) a
> where =A0a.bu=3Db.bu
> and a.ndc =3D b.ndc
>
> 3. =A0 I need to create a temp table with the unique ids in order to
> compare the ci table to the duplicates.
> I create the temp table with the SELECT from above that now has the
> duplicate records in it with the id.
> CREATE TABLE =A0ci_tmp AS
> (SELECT c.id,c.bu,c.zonenm,c.ndc
> FROM ci c
> GROUP BY c.bu,c.ndc
> HAVING =A0COUNT(concat(c.bu,c.ndc) )>1)
>
> 4. =A0Next I have to run my delete query matching against the ids in the
> temp table.
> AT this point I am not sure exactly how to do this or what I need to
> delete.
> This is what I have but it isn't right because it doesn't delete any
> records.
>
> DELETE FROM cart_inventory
> WHERE id IN (select id
> FROM cart_inventory_tmp
> GROUP BY bu,nationaldrugcode
> HAVING COUNT(concat(bu,nationaldrugcode) )>1)
>
> I also tried =A0 deleting using max(id) but I don't know if it requires
> a self-join and I think the above process might be easier?
>
> delete =A0FROM cart_inventory as as a
> max(id)
> where =A0concat(a.bu, a.ndc)=3Dconcat(b.bu,b.ndc)
>
> If you could explain the process of Query#4 what I am trying to match
> against that might help but I basically understand
> that you need to get the id of one of the duplicates from the temp
> table and match it against the ci table and delete that id.
> It seems like it should work but it doesn't because I run query # 4
> and do the select for the dupes and they are all still there..
>
> thanks,


1) are the rows truely DUPLICATE or do you just have a bu,ndc
duplicate - are there other columns and are they also duplicate or are
they unique?  If "unique", which one is correct?

See: http://dev.mysql.com/doc/refman/5.5/en/alter-table.html and
search for IGNORE

"IGNORE is a MySQL extension to standard SQL. It controls how ALTER
TABLE works if there are duplicates on unique keys in the new table or
if warnings occur when strict mode is enabled. If IGNORE is not
specified, the copy is aborted and rolled back if duplicate-key errors
occur. If IGNORE is specified, only the first row is used of rows with
duplicates on a unique key, The other conflicting rows are deleted.
Incorrect values are truncated to the closest matching acceptable
value."

ALTER IGNORE TABLE ci ADD UNIQUE KEY (bu,ndc ) ;

How it knows which one is "first" I do not know...

I would test this before actually executingto ensure you are purging
the correct data.

Next step - figure out why you are getting unwanted dupes and fix the
problem.

0
onedbguru
1/4/2011 1:13:49 AM
On Jan 3, 5:13=A0pm, onedbguru <onedbg...@yahoo.com> wrote:
> On Jan 3, 7:37=A0pm, jr <jlro...@yahoo.com> wrote:
>
>
>
>
>
>
>
>
>
> > I need to delete the dupes across bu's. =A0The records are unique based
> > on bu+ndc but there are 400+ duplicates..
> > There is a sequential id that is unique. =A0I am trying to remove these
> > 400+ records.
>
> > 1. =A0SELECT *
> > FROM ci
> > GROUP BY bu,ndc
> > HAVING COUNT(concat(bu,ndc) )>1
>
> > This query yields 400+ dupes which I need to remove.
>
> > 2.
> > I could do this query in Oracle but not in MySQL.
> > You cannot delete from the same table as in the subquery.
>
> > =A0DELETE FROM ci =A0b
> > WHERE EXISTS ((
> > SELECT bu,ndc, count(1)
> > FROM ci
> > GROUP BY bu,ndc
> > HAVING =A0COUNT(1)>1
> > ) a
> > where =A0a.bu=3Db.bu
> > and a.ndc =3D b.ndc
>
> > 3. =A0 I need to create a temp table with the unique ids in order to
> > compare the ci table to the duplicates.
> > I create the temp table with the SELECT from above that now has the
> > duplicate records in it with the id.
> > CREATE TABLE =A0ci_tmp AS
> > (SELECT c.id,c.bu,c.zonenm,c.ndc
> > FROM ci c
> > GROUP BY c.bu,c.ndc
> > HAVING =A0COUNT(concat(c.bu,c.ndc) )>1)
>
> > 4. =A0Next I have to run my delete query matching against the ids in th=
e
> > temp table.
> > AT this point I am not sure exactly how to do this or what I need to
> > delete.
> > This is what I have but it isn't right because it doesn't delete any
> > records.
>
> > DELETE FROM cart_inventory
> > WHERE id IN (select id
> > FROM cart_inventory_tmp
> > GROUP BY bu,nationaldrugcode
> > HAVING COUNT(concat(bu,nationaldrugcode) )>1)
>
> > I also tried =A0 deleting using max(id) but I don't know if it requires
> > a self-join and I think the above process might be easier?
>
> > delete =A0FROM cart_inventory as as a
> > max(id)
> > where =A0concat(a.bu, a.ndc)=3Dconcat(b.bu,b.ndc)
>
> > If you could explain the process of Query#4 what I am trying to match
> > against that might help but I basically understand
> > that you need to get the id of one of the duplicates from the temp
> > table and match it against the ci table and delete that id.
> > It seems like it should work but it doesn't because I run query # 4
> > and do the select for the dupes and they are all still there..
>
> > thanks,
>
> 1) are the rows truely DUPLICATE or do you just have a bu,ndc
> duplicate - are there other columns and are they also duplicate or are
> they unique? =A0If "unique", which one is correct?
>
> See:http://dev.mysql.com/doc/refman/5.5/en/alter-table.htmland
> search for IGNORE
>
> "IGNORE is a MySQL extension to standard SQL. It controls how ALTER
> TABLE works if there are duplicates on unique keys in the new table or
> if warnings occur when strict mode is enabled. If IGNORE is not
> specified, the copy is aborted and rolled back if duplicate-key errors
> occur. If IGNORE is specified, only the first row is used of rows with
> duplicates on a unique key, The other conflicting rows are deleted.
> Incorrect values are truncated to the closest matching acceptable
> value."
>
> ALTER IGNORE TABLE ci ADD UNIQUE KEY (bu,ndc ) ;
>
> How it knows which one is "first" I do not know...
>
> I would test this before actually executingto ensure you are purging
> the correct data.
>
> Next step - figure out why you are getting unwanted dupes and fix the
> problem.

it is bu,ndc duplicates I am trying to get rid of, this is data
cleansing, the first or the 2nd duplicate doesn't matter, there is no
duplicate on the unique key
only duplicate on the bu,ndc.  The concat(bu,ndc) is how I determine
the row is unique.  The id was assigned as I uploaded the data.
Should I just use the max(id) if so how do I set up the self-join?
thanks,
0
jr
1/4/2011 1:27:29 AM
On Jan 3, 5:13=A0pm, onedbguru <onedbg...@yahoo.com> wrote:
> On Jan 3, 7:37=A0pm, jr <jlro...@yahoo.com> wrote:
>
>
>
>
>
>
>
>
>
> > I need to delete the dupes across bu's. =A0The records are unique based
> > on bu+ndc but there are 400+ duplicates..
> > There is a sequential id that is unique. =A0I am trying to remove these
> > 400+ records.
>
> > 1. =A0SELECT *
> > FROM ci
> > GROUP BY bu,ndc
> > HAVING COUNT(concat(bu,ndc) )>1
>
> > This query yields 400+ dupes which I need to remove.
>
> > 2.
> > I could do this query in Oracle but not in MySQL.
> > You cannot delete from the same table as in the subquery.
>
> > =A0DELETE FROM ci =A0b
> > WHERE EXISTS ((
> > SELECT bu,ndc, count(1)
> > FROM ci
> > GROUP BY bu,ndc
> > HAVING =A0COUNT(1)>1
> > ) a
> > where =A0a.bu=3Db.bu
> > and a.ndc =3D b.ndc
>
> > 3. =A0 I need to create a temp table with the unique ids in order to
> > compare the ci table to the duplicates.
> > I create the temp table with the SELECT from above that now has the
> > duplicate records in it with the id.
> > CREATE TABLE =A0ci_tmp AS
> > (SELECT c.id,c.bu,c.zonenm,c.ndc
> > FROM ci c
> > GROUP BY c.bu,c.ndc
> > HAVING =A0COUNT(concat(c.bu,c.ndc) )>1)
>
> > 4. =A0Next I have to run my delete query matching against the ids in th=
e
> > temp table.
> > AT this point I am not sure exactly how to do this or what I need to
> > delete.
> > This is what I have but it isn't right because it doesn't delete any
> > records.
>
> > DELETE FROM cart_inventory
> > WHERE id IN (select id
> > FROM cart_inventory_tmp
> > GROUP BY bu,nationaldrugcode
> > HAVING COUNT(concat(bu,nationaldrugcode) )>1)
>
> > I also tried =A0 deleting using max(id) but I don't know if it requires
> > a self-join and I think the above process might be easier?
>
> > delete =A0FROM cart_inventory as as a
> > max(id)
> > where =A0concat(a.bu, a.ndc)=3Dconcat(b.bu,b.ndc)
>
> > If you could explain the process of Query#4 what I am trying to match
> > against that might help but I basically understand
> > that you need to get the id of one of the duplicates from the temp
> > table and match it against the ci table and delete that id.
> > It seems like it should work but it doesn't because I run query # 4
> > and do the select for the dupes and they are all still there..
>
> > thanks,
>
> 1) are the rows truely DUPLICATE or do you just have a bu,ndc
> duplicate - are there other columns and are they also duplicate or are
> they unique? =A0If "unique", which one is correct?
>
> See:http://dev.mysql.com/doc/refman/5.5/en/alter-table.htmland
> search for IGNORE
>
> "IGNORE is a MySQL extension to standard SQL. It controls how ALTER
> TABLE works if there are duplicates on unique keys in the new table or
> if warnings occur when strict mode is enabled. If IGNORE is not
> specified, the copy is aborted and rolled back if duplicate-key errors
> occur. If IGNORE is specified, only the first row is used of rows with
> duplicates on a unique key, The other conflicting rows are deleted.
> Incorrect values are truncated to the closest matching acceptable
> value."
>
> ALTER IGNORE TABLE ci ADD UNIQUE KEY (bu,ndc ) ;
>
> How it knows which one is "first" I do not know...
>
> I would test this before actually executingto ensure you are purging
> the correct data.
>
> Next step - figure out why you are getting unwanted dupes and fix the
> problem.

I tried the ALTER IGNORE  and changed the unique key to the (bu,ndc)
on a backup table.
It changed the number of records in ci by 169 so I am afraid to do it
like that.
If I could figure out how to delete using the max(id) I would do it
that way.
0
jr
1/4/2011 1:59:19 AM
On 2011-01-04 01:37, jr wrote:
[...]
> 2.
> I could do this query in Oracle but not in MySQL.
> You cannot delete from the same table as in the subquery.
> 
> 
>  DELETE FROM ci  b
> WHERE EXISTS ((
> SELECT bu,ndc, count(1)
> FROM ci
> GROUP BY bu,ndc
> HAVING  COUNT(1)>1
> ) a
> where  a.bu=b.bu
> and a.ndc = b.ndc
> 

You can hide the table at a deeper nesting level, but it will probably
be very slow. Another option is to delete from a join (non-standard)
like (untested):

delete b.*
from ci as b
join (
  SELECT bu,ndc
  FROM ci
  GROUP BY bu,ndc
  HAVING  COUNT(1)>1
) as a
    on a.bu=b.bu
   and a.ndc = b.ndc

If bu, ndu is a candidate key, you might want to add a unique constraint:

    alter table ci add constraint ... unique (bu, ndc);

to enforce this.

[...]

/Lennart
0
Lennart
1/4/2011 8:35:16 AM
On Jan 4, 12:35=A0am, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2011-01-04 01:37, jr wrote:
> [...]
>
> > 2.
> > I could do this query in Oracle but not in MySQL.
> > You cannot delete from the same table as in the subquery.
>
> > =A0DELETE FROM ci =A0b
> > WHERE EXISTS ((
> > SELECT bu,ndc, count(1)
> > FROM ci
> > GROUP BY bu,ndc
> > HAVING =A0COUNT(1)>1
> > ) a
> > where =A0a.bu=3Db.bu
> > and a.ndc =3D b.ndc
>
> You can hide the table at a deeper nesting level, but it will probably
> be very slow. Another option is to delete from a join (non-standard)
> like (untested):
>
> delete b.*
> from ci as b
> join (
> =A0 SELECT bu,ndc
> =A0 FROM ci
> =A0 GROUP BY bu,ndc
> =A0 HAVING =A0COUNT(1)>1
> ) as a
> =A0 =A0 on a.bu=3Db.bu
> =A0 =A0and a.ndc =3D b.ndc
>
> If bu, ndu is a candidate key, you might want to add a unique constraint:
>
> =A0 =A0 alter table ci add constraint ... unique (bu, ndc);
>
> to enforce this.
>
> [...]
>
> /Lennart

The query is beautiful although it deleted 1085 rows.   When I did the
first query to locate duplicates it found only 487.  Why is
that? ?????
Where did the extra
This is the query I used to find the dupes.  Is that wrong or is your
query wrong?  Did it delete the dupe and the original record as well?

select bu,ndc, count(*)
from ci
group by bu,ndc
having COUNT(concat(bu,nationaldrugcode)) > 1



I wouldn't hide the table at a deeper level because users are using
the web page and it is somewhat slow already because of a UNION
query.  I could try it if all else fails.  I will try the unique
constraint.

thanks,
0
jr
1/4/2011 3:37:05 PM
On 2011-01-04 16:37, jr wrote:
[...]
> 
> The query is beautiful although it deleted 1085 rows.   When I did the
> first query to locate duplicates it found only 487.  Why is
> that? ?????
> Where did the extra
> This is the query I used to find the dupes.  Is that wrong or is your
> query wrong?  Did it delete the dupe and the original record as well?
> 
> select bu,ndc, count(*)
> from ci
> group by bu,ndc
> having COUNT(concat(bu,nationaldrugcode)) > 1
> 
> 

I should have look more carefully, I just translated your attempt:

DELETE FROM ci  b
WHERE EXISTS ((
SELECT bu,ndc, count(1)
FROM ci
GROUP BY bu,ndc
HAVING  COUNT(1)>1
) a
where  a.bu=b.bu
and a.ndc = b.ndc

into

delete b.*
from ci as b
join (
  SELECT bu,ndc
  FROM ci
  GROUP BY bu,ndc
  HAVING  COUNT(1)>1
) as a
    on a.bu=b.bu
   and a.ndc = b.ndc

It is probably not what you want because it will delete *all* rows that
are duplicated (including the first occurrence). Compare the result from:

SELECT *
FROM ci  b
WHERE EXISTS (
    SELECT bu,ndc, count(1)
    FROM ci
    GROUP BY bu,ndc
    HAVING COUNT(1)>1
) a
where a.bu=b.bu
and a.ndc = b.ndc

with:

select b.*
from ci as b
join (
  SELECT bu,ndc
  FROM ci
  GROUP BY bu,ndc
  HAVING  COUNT(1)>1
) as a
    on a.bu=b.bu
   and a.ndc = b.ndc

> 
> I wouldn't hide the table at a deeper level because users are using
> the web page and it is somewhat slow already because of a UNION
> query.  I could try it if all else fails.

No, you misunderstood me. What I meant was that you can hide the table
inside the delete stmt as in:

SELECT *
FROM ci  b
WHERE EXISTS (
    select * FROM (
        SELECT bu,ndc, count(1)
        FROM ci
        GROUP BY bu,ndc
        HAVING COUNT(1)>1
    ) AS X
) a
where a.bu=b.bu
and a.ndc = b.ndc

but this will also delete more rows than you want (I guess). You will
have to enumerate the duplicate rows somehow (the sequential unique id
is probably your best option). Here's one attempt:

delete b.*
from ci as b
where exists (
    select 1
    from ci as c
    where c.bu = b.bu
      and c.ndc = b.ndc
      and c.id < b.id
)

But mysql does not allow this, so we'll rewrite that as a join:

delete b.*
from ci as b
join ci as c
    on c.bu = b.bu
    and c.ndc = b.ndc
    and c.id < b.id

Example:

create table ci (
    id int not null primary key,
    bu int not null,
    ndc int not null
) engine = innodb;

insert into ci (id, bu, ndc)
values (1,1,1),(2,1,1),(3,2,1),(4,2,2),(5,2,2),(6,1,1);

The first query will delete *all* duplicates, i.e. all rows but the one
with id = 3:

mysql> select b.*
    -> from ci as b
    -> join (
    ->   SELECT bu,ndc
    ->   FROM ci
    ->   GROUP BY bu,ndc
    ->   HAVING  COUNT(1)>1
    -> ) as a
    ->     on a.bu=b.bu
    ->    and a.ndc = b.ndc;
+----+----+-----+
| id | bu | ndc |
+----+----+-----+
|  1 |  1 |   1 |
|  2 |  1 |   1 |
|  4 |  2 |   2 |
|  5 |  2 |   2 |
|  6 |  1 |   1 |
+----+----+-----+

The rewritten query leaves the duplicates with the lowest id:

mysql> select b.* from ci as b
    -> join ci as c
    ->     on c.bu = b.bu
    ->     and c.ndc = b.ndc
    ->     and c.id < b.id;
+----+----+-----+
| id | bu | ndc |
+----+----+-----+
|  2 |  1 |   1 |
|  5 |  2 |   2 |
|  6 |  1 |   1 |
|  6 |  1 |   1 |
+----+----+-----+
4 rows in set (0.05 sec)

mysql> delete b.*
    -> from ci as b
    -> join ci as c
    ->    on c.bu = b.bu
    ->   and c.ndc = b.ndc
    ->   and c.id < b.id;
Query OK, 3 rows affected (0.05 sec)

mysql> select * from ci;
+----+----+-----+
| id | bu | ndc |
+----+----+-----+
|  1 |  1 |   1 |
|  3 |  2 |   1 |
|  4 |  2 |   2 |
+----+----+-----+
3 rows in set (0.00 sec)

Don't just take the code and execute it, try understand the similarities
and differences from the first attempt.

HTH
/Lennart
0
Lennart
1/4/2011 9:19:57 PM
On 2011-01-04 22:19, Lennart Jonsson wrote:
[...]
> 
> SELECT *
> FROM ci  b
> WHERE EXISTS (
>     SELECT bu,ndc, count(1)
>     FROM ci
>     GROUP BY bu,ndc
>     HAVING COUNT(1)>1
> ) a
> where a.bu=b.bu
> and a.ndc = b.ndc
> 

I get an syntax error here, so I guess uncorrelated exists subqueries
are not allowed either. You can rewrite this to:

SELECT b.* FROM ci as b where exists ( select bu,ndc from ci as a where
a.bu = b.bu and a.ndc = b.ndc group by  bu,ndc having count(1) > 1);
+----+----+-----+
| id | bu | ndc |
+----+----+-----+
|  1 |  1 |   1 |
|  2 |  1 |   1 |
|  4 |  2 |   2 |
|  5 |  2 |   2 |
|  6 |  1 |   1 |
+----+----+-----+

/Lennart
0
Lennart
1/4/2011 9:38:30 PM
On 2011-01-04 22:38, Lennart Jonsson wrote:
> On 2011-01-04 22:19, Lennart Jonsson wrote:
> [...]
>> 
>> SELECT *
>> FROM ci  b
>> WHERE EXISTS (
>>     SELECT bu,ndc, count(1)
>>     FROM ci
>>     GROUP BY bu,ndc
>>     HAVING COUNT(1)>1
>> ) a
>> where a.bu=b.bu
>> and a.ndc = b.ndc
>> 
> 
> I get an syntax error here

Never mind, there where several other reasons for the syntax error, I
just did not notice them.
0
Lennart
1/5/2011 11:53:49 PM
On Jan 5, 3:53=A0pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2011-01-04 22:38, Lennart Jonsson wrote:
>
>
>
>
>
>
>
>
>
> > On 2011-01-04 22:19, Lennart Jonsson wrote:
> > [...]
>
> >> SELECT *
> >> FROM ci =A0b
> >> WHERE EXISTS (
> >> =A0 =A0 SELECT bu,ndc, count(1)
> >> =A0 =A0 FROM ci
> >> =A0 =A0 GROUP BY bu,ndc
> >> =A0 =A0 HAVING COUNT(1)>1
> >> ) a
> >> where a.bu=3Db.bu
> >> and a.ndc =3D b.ndc
>
> > I get an syntax error here
>
> Never mind, there where several other reasons for the syntax error, I
> just did not notice them.

I do get it, thank, you finished the first query that I was trying to
do even though it was wrong.
If I could have done it then I could have seen it was wrong.  I did
want to do the self-join.
  I get it you can't delete from the same table as a subquery.  Thanks
for explaining the rest of it.
0
jr
1/6/2011 1:55:16 PM
On Jan 4, 1:38=A0pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2011-01-04 22:19, Lennart Jonsson wrote:
> [...]
>
>
>
> > SELECT *
> > FROM ci =A0b
> > WHERE EXISTS (
> > =A0 =A0 SELECT bu,ndc, count(1)
> > =A0 =A0 FROM ci
> > =A0 =A0 GROUP BY bu,ndc
> > =A0 =A0 HAVING COUNT(1)>1
> > ) a
> > where a.bu=3Db.bu
> > and a.ndc =3D b.ndc
>
> I get an syntax error here, so I guess uncorrelated exists subqueries
> are not allowed either. You can rewrite this to:
>
> SELECT b.* FROM ci as b where exists ( select bu,ndc from ci as a where
> a.bu =3D b.bu and a.ndc =3D b.ndc group by =A0bu,ndc having count(1) > 1)=
;
> +----+----+-----+
> | id | bu | ndc |
> +----+----+-----+
> | =A01 | =A01 | =A0 1 |
> | =A02 | =A01 | =A0 1 |
> | =A04 | =A02 | =A0 2 |
> | =A05 | =A02 | =A0 2 |
> | =A06 | =A01 | =A0 1 |
> +----+----+-----+
>
> /Lennart

I ran through all your exercises ;-)
I see why my first query didn't work there were 2 WHERE clauses.
The 2nd query of yours deletes all duplicates because it is an equi-
join on bu&ndc.
The 3rd query,  I did get a syntax error on the select:
select *
from ci  b
where exists(
select bu,ndc, count(1)
from ci
group by bu, ndc
having count(1)>1
)a
where a.bu=3Db.bu
and a.ndc=3Db.ndc

and I also tried it as a delete:
delete * from ci b
where exists(
select bu,ndc, count(1)
from ci
group by bu, ndc
having count(1)>1
)a
where a.bu=3Db.bu
and a.ndc=3Db.ndc,
I got syntax errors.  You mentioned some other reason other than a
syntax error?

The last query worked as you said.
joining also on the id.
It worked really well on one pass!!!!!!!!!!!

0
jr
1/12/2011 11:36:42 PM
On 2011-01-13 00:36, jr wrote:
[...]
> The 3rd query,  I did get a syntax error on the select:
> select *
> from ci  b
> where exists(
> select bu,ndc, count(1)
> from ci
> group by bu, ndc
> having count(1)>1
> )a
> where a.bu=b.bu
> and a.ndc=b.ndc
>
[...]
> I got syntax errors.  You mentioned some other reason other than a
> syntax error?
> 

You already mentioned the two where clauses, fixing that leaves us with:

.... exists (
    select bu,ndc, count(1)
    from ci
    group by bu, ndc
    having count(1)>1
) a

exists is a predicate ( sometimes referred to as propositional function,
you can think of it as an anonymous function returning true/false ) and
you can't give it a name ( there is no point in doing so either ).
Furthermore bu, ndc, etc inside the predicate is not visible outside the
predicate, so you can't reference them like you do in:

  where a.bu=b.bu
    and a.ndc=b.ndc

You can however reference variables outside of the predicate from within
the predicate, so

select *
from ci  b
where exists(
    select bu,ndc, count(1)
    from ci as a

    where a.bu=b.bu
    and a.ndc=b.ndc

    group by bu, ndc
    having count(1)>1
)

is syntactically correct (will still give you the wrong result).


/Lennart
0
Lennart
1/13/2011 6:10:26 AM
On Jan 4, 1:19=A0pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2011-01-04 16:37, jr wrote:
> [...]
>
>
>
> > The query is beautiful although it deleted 1085 rows. =A0 When I did th=
e
> > first query to locate duplicates it found only 487. =A0Why is
> > that? ?????
> > Where did the extra
> > This is the query I used to find the dupes. =A0Is that wrong or is your
> > query wrong? =A0Did it delete the dupe and the original record as well?
>
> > select bu,ndc, count(*)
> > from ci
> > group by bu,ndc
> > having COUNT(concat(bu,nationaldrugcode)) > 1
>
> I should have look more carefully, I just translated your attempt:
>
> DELETE FROM ci =A0b
> WHERE EXISTS ((
> SELECT bu,ndc, count(1)
> FROM ci
> GROUP BY bu,ndc
> HAVING =A0COUNT(1)>1
> ) a
> where =A0a.bu=3Db.bu
> and a.ndc =3D b.ndc
>
> into
>
> delete b.*
> from ci as b
> join (
> =A0 SELECT bu,ndc
> =A0 FROM ci
> =A0 GROUP BY bu,ndc
> =A0 HAVING =A0COUNT(1)>1
> ) as a
> =A0 =A0 on a.bu=3Db.bu
> =A0 =A0and a.ndc =3D b.ndc
>
> It is probably not what you want because it will delete *all* rows that
> are duplicated (including the first occurrence). Compare the result from:
>
> SELECT *
> FROM ci =A0b
> WHERE EXISTS (
> =A0 =A0 SELECT bu,ndc, count(1)
> =A0 =A0 FROM ci
> =A0 =A0 GROUP BY bu,ndc
> =A0 =A0 HAVING COUNT(1)>1
> ) a
> where a.bu=3Db.bu
> and a.ndc =3D b.ndc
>
> with:
>
> select b.*
> from ci as b
> join (
> =A0 SELECT bu,ndc
> =A0 FROM ci
> =A0 GROUP BY bu,ndc
> =A0 HAVING =A0COUNT(1)>1
> ) as a
> =A0 =A0 on a.bu=3Db.bu
> =A0 =A0and a.ndc =3D b.ndc
>
>
>
> > I wouldn't hide the table at a deeper level because users are using
> > the web page and it is somewhat slow already because of a UNION
> > query. =A0I could try it if all else fails.
>
> No, you misunderstood me. What I meant was that you can hide the table
> inside the delete stmt as in:
>
> SELECT *
> FROM ci =A0b
> WHERE EXISTS (
> =A0 =A0 select * FROM (
> =A0 =A0 =A0 =A0 SELECT bu,ndc, count(1)
> =A0 =A0 =A0 =A0 FROM ci
> =A0 =A0 =A0 =A0 GROUP BY bu,ndc
> =A0 =A0 =A0 =A0 HAVING COUNT(1)>1
> =A0 =A0 ) AS X
> ) a
> where a.bu=3Db.bu
> and a.ndc =3D b.ndc
>
> but this will also delete more rows than you want (I guess). You will
> have to enumerate the duplicate rows somehow (the sequential unique id
> is probably your best option). Here's one attempt:
>
> delete b.*
> from ci as b
> where exists (
> =A0 =A0 select 1
> =A0 =A0 from ci as c
> =A0 =A0 where c.bu =3D b.bu
> =A0 =A0 =A0 and c.ndc =3D b.ndc
> =A0 =A0 =A0 and c.id < b.id
> )
>
> But mysql does not allow this, so we'll rewrite that as a join:
>
> delete b.*
> from ci as b
> join ci as c
> =A0 =A0 on c.bu =3D b.bu
> =A0 =A0 and c.ndc =3D b.ndc
> =A0 =A0 and c.id < b.id
>
> Example:
>
> create table ci (
> =A0 =A0 id int not null primary key,
> =A0 =A0 bu int not null,
> =A0 =A0 ndc int not null
> ) engine =3D innodb;
>
> insert into ci (id, bu, ndc)
> values (1,1,1),(2,1,1),(3,2,1),(4,2,2),(5,2,2),(6,1,1);
>
> The first query will delete *all* duplicates, i.e. all rows but the one
> with id =3D 3:
>
> mysql> select b.*
> =A0 =A0 -> from ci as b
> =A0 =A0 -> join (
> =A0 =A0 -> =A0 SELECT bu,ndc
> =A0 =A0 -> =A0 FROM ci
> =A0 =A0 -> =A0 GROUP BY bu,ndc
> =A0 =A0 -> =A0 HAVING =A0COUNT(1)>1
> =A0 =A0 -> ) as a
> =A0 =A0 -> =A0 =A0 on a.bu=3Db.bu
> =A0 =A0 -> =A0 =A0and a.ndc =3D b.ndc;
> +----+----+-----+
> | id | bu | ndc |
> +----+----+-----+
> | =A01 | =A01 | =A0 1 |
> | =A02 | =A01 | =A0 1 |
> | =A04 | =A02 | =A0 2 |
> | =A05 | =A02 | =A0 2 |
> | =A06 | =A01 | =A0 1 |
> +----+----+-----+
>
> The rewritten query leaves the duplicates with the lowest id:
>
> mysql> select b.* from ci as b
> =A0 =A0 -> join ci as c
> =A0 =A0 -> =A0 =A0 on c.bu =3D b.bu
> =A0 =A0 -> =A0 =A0 and c.ndc =3D b.ndc
> =A0 =A0 -> =A0 =A0 and c.id < b.id;
> +----+----+-----+
> | id | bu | ndc |
> +----+----+-----+
> | =A02 | =A01 | =A0 1 |
> | =A05 | =A02 | =A0 2 |
> | =A06 | =A01 | =A0 1 |
> | =A06 | =A01 | =A0 1 |
> +----+----+-----+
> 4 rows in set (0.05 sec)
>
> mysql> delete b.*
> =A0 =A0 -> from ci as b
> =A0 =A0 -> join ci as c
> =A0 =A0 -> =A0 =A0on c.bu =3D b.bu
> =A0 =A0 -> =A0 and c.ndc =3D b.ndc
> =A0 =A0 -> =A0 and c.id < b.id;
> Query OK, 3 rows affected (0.05 sec)
>
> mysql> select * from ci;
> +----+----+-----+
> | id | bu | ndc |
> +----+----+-----+
> | =A01 | =A01 | =A0 1 |
> | =A03 | =A02 | =A0 1 |
> | =A04 | =A02 | =A0 2 |
> +----+----+-----+
> 3 rows in set (0.00 sec)
>
> Don't just take the code and execute it, try understand the similarities
> and differences from the first attempt.
>
> HTH
> /Lennart

Lennart,
Regarding this last delete query, it worked on your small sample
database but  I tried it on my db of 46825 records and it didn't work.
It left me with only 24,571 records.  I did a query to get the num of
duplicates and there were only 9996.  Most of those were only
duplicates, but there were some triplicates etc.  So it deleted way
too many and I had to kill the server eventually.   I came up with 3
other queries that does the deleting in passes by comparing a table
with the duplicate ids with a table containing the max(id)s.  It
seemed to work except something is wrong there also. It deleted all
the duplicates, triplcates, quadruplicates, etc.  It should have only
deleted one of the duplicates with the max(id) and left the original
duplicate.

1.create table ci_tmp1 as
(select id,bu,ndc, count(*)
from ci
group by bu,ndc
having COUNT(concat(bu,ndc)) > 1
)

2.create table ci_tmp2 as(select max(c.id) from ci c, ci_tmp1 t
WHERE t.bu=3Dc.bu AND t.ndc=3Dc.ndc
GROUP BY  t.bu, t.ndc)

3.DELETE FROM ci WHERE id IN (select * from ci_tmp2)

This delete query got near to the correct amount of duplicates
34,576.   If you subtract 9996 from 46825  you get 36,829.
The only problem with this result was I was expecting to have the
triplicates and quadrupleticates left in the ci table and I would have
to run through
the 3 queries again.   When I checked the ci table there were no more
duplicates.   What I was hoping was that your query results number
would match with
my way of doing it in 3 passes and I would know I could use your query
but now I'm not even sure if my queries are correct?  What do you
think?
Regards,  Janis
..
0
jr
1/17/2011 9:41:17 PM
On Jan 12, 10:10=A0pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2011-01-13 00:36, jr wrote:
> [...]
>
>
>
>
>
>
>
>
>
> > The 3rd query, =A0I did get a syntax error on the select:
> > select *
> > from ci =A0b
> > where exists(
> > select bu,ndc, count(1)
> > from ci
> > group by bu, ndc
> > having count(1)>1
> > )a
> > where a.bu=3Db.bu
> > and a.ndc=3Db.ndc
>
> [...]
> > I got syntax errors. =A0You mentioned some other reason other than a
> > syntax error?
>
> You already mentioned the two where clauses, fixing that leaves us with:
>
> ... exists (
> =A0 =A0 select bu,ndc, count(1)
> =A0 =A0 from ci
> =A0 =A0 group by bu, ndc
> =A0 =A0 having count(1)>1
> ) a
>
> exists is a predicate ( sometimes referred to as propositional function,
> you can think of it as an anonymous function returning true/false ) and
> you can't give it a name ( there is no point in doing so either ).
> Furthermore bu, ndc, etc inside the predicate is not visible outside the
> predicate, so you can't reference them like you do in:
>
> =A0 where a.bu=3Db.bu
> =A0 =A0 and a.ndc=3Db.ndc
>
> You can however reference variables outside of the predicate from within
> the predicate, so
>
> select *
> from ci =A0b
> where exists(
> =A0 =A0 select bu,ndc, count(1)
> =A0 =A0 from ci as a
>
> =A0 =A0 where a.bu=3Db.bu
> =A0 =A0 and a.ndc=3Db.ndc
>
> =A0 =A0 group by bu, ndc
> =A0 =A0 having count(1)>1
> )
>
> is syntactically correct (will still give you the wrong result).
>
> /Lennart

thanks,
0
jr
1/17/2011 9:45:02 PM
On 2011-01-17 22:41, jr wrote:
[...]
> Lennart,
> Regarding this last delete query, it worked on your small sample
> database but  I tried it on my db of 46825 records and it didn't work.
> It left me with only 24,571 records.  I did a query to get the num of
> duplicates and there were only 9996.  Most of those were only
> duplicates, but there were some triplicates etc.  So it deleted way
> too many and I had to kill the server eventually.   I came up with 3
> other queries that does the deleting in passes by comparing a table
> with the duplicate ids with a table containing the max(id)s.  It
> seemed to work except something is wrong there also. It deleted all
> the duplicates, triplcates, quadruplicates, etc.  It should have only
> deleted one of the duplicates with the max(id) and left the original
> duplicate.
> 
> 1.create table ci_tmp1 as
> (select id,bu,ndc, count(*)
> from ci
> group by bu,ndc
> having COUNT(concat(bu,ndc)) > 1
> )
> 

This query is illegal and will give you random results. It will only
work if id is functionally dependent of bu, ndc. You can prevent this
misbehaviour by enabling ONLY_FULL_GROUP_BY in sql_mode, mysql will then
return an error instead of a wrong result.

/Lennart

[...]

0
Lennart
1/18/2011 5:54:07 AM
On Jan 17, 9:54=A0pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2011-01-17 22:41, jr wrote:
> [...]
>
>
>
>
>
>
>
>
>
> > Lennart,
> > Regarding this last delete query, it worked on your small sample
> > database but =A0I tried it on my db of 46825 records and it didn't work=
..
> > It left me with only 24,571 records. =A0I did a query to get the num of
> > duplicates and there were only 9996. =A0Most of those were only
> > duplicates, but there were some triplicates etc. =A0So it deleted way
> > too many and I had to kill the server eventually. =A0 I came up with 3
> > other queries that does the deleting in passes by comparing a table
> > with the duplicate ids with a table containing the max(id)s. =A0It
> > seemed to work except something is wrong there also. It deleted all
> > the duplicates, triplcates, quadruplicates, etc. =A0It should have only
> > deleted one of the duplicates with the max(id) and left the original
> > duplicate.
>
> > 1.create table ci_tmp1 as
> > (select id,bu,ndc, count(*)
> > from ci
> > group by bu,ndc
> > having COUNT(concat(bu,ndc)) > 1
> > )
>
> This query is illegal and will give you random results. It will only
> work if id is functionally dependent of bu, ndc. You can prevent this
> misbehaviour by enabling ONLY_FULL_GROUP_BY in sql_mode, mysql will then
> return an error instead of a wrong result.
>
> /Lennart
>
> [...]

thanks for explaining it.  It was very useful.
0
jr
1/18/2011 5:09:23 PM
On 2011-01-18 18:09, jr wrote:
[...]
>> This query is illegal and will give you random results. It will only
>> work if id is functionally dependent of bu, ndc. You can prevent this
>> misbehaviour by enabling ONLY_FULL_GROUP_BY in sql_mode, mysql will then
>> return an error instead of a wrong result.
>>
>> /Lennart
>>
>> [...]
> 
> thanks for explaining it.  It was very useful.

You can verify by:

set sql_mode='';

create table t (
    id int not null primary key,
    bu int not null,
    ndc int not null
) engine = innodb;

insert into t (id, bu, ndc) values (1,1,1),(2,1,1),(3,1,1);

select id, bu, ndc, count(1) from t group by bu, ndc;
+----+----+-----+----------+
| id | bu | ndc | count(1) |
+----+----+-----+----------+
|  1 |  1 |   1 |        3 |
+----+----+-----+----------+
1 row in set (0.00 sec)

id might be any one of 1, 2 or 3

/Lennart

0
Lennart
1/18/2011 7:25:10 PM
Reply:
Similar Artilces:

How to Measuring the Temperature of the PXI-1045 Controllers Using LabVIEW
Hi, &nbsp; I need to continuously monitor the temperature of the PXI-1045 controller. I have&nbsp;had a look&nbsp;&nbsp;the vi supplied by NI&nbsp;on <a href="http://zone.ni.com/devzone/cda/epd/p/id/4538#0requirements" target="_blank">http://zone.ni.com/devzone/cda/epd/p/id/4538#0requirements</a>&nbsp;which is developed for PXI-8196/95 and the PXI-8186/87 controllers. Can&nbsp;similar temperature measurement be done on&nbsp;PXI-1045? &nbsp; Regards &nbsp; Mehran Thanks Tom, I am&nbsp;more clear now. The NI controlle...

How do you recover deleted pictures on Samsung tablet and/or external hard drive
A friend just left me a message that he copied pictures from his Samsung tablet to an external hard drive, and then, he *thought* he deleted the pictures off the Samsung tablet, but now they're nowhere to be found. I'll get more information from him, but, do you have initial advice for me as to what he should do to recover the lost pictures? "Jonathan Williams" <jwbwilliams@comcast.net> wrote in message news:m314h1$8fo$2@news.albasani.net... >A friend just left me a message that he copied pictures from his Samsung > tablet to an external hard drive,...

delete on delete !
Hi, If I do another delete on an object which has been deleted earlier, then how is the system expected to behave ? Is it an unpredictable behavior ?? Thanks Sandeep Sandeep Grover wrote: > Hi, > > If I do another delete on an object which has been deleted earlier, then > how is > the system expected to behave ? > > Is it an unpredictable behavior ?? > Yes, unpredictable. Some will choke, some destructors SEGV, it goes on and on. Sandeep Grover wrote: > If I do another delete on an object which has been deleted earlier, then > how is the system expect...

Permanently deleting libraries
Is there a way to permanently delete libraries? Using Thomas Rast's port browser, you can go in and find old deleted libraries in port memory. I know the OS marks as deleted so that flash memory is cycled properly, but this seems to be what is giving me problems with installing ARM libraries into port 2. If I store the library, and then delete it, and then store it again using the ARM toolbox FixSTO it always works. So what would be the way to go delete those libraries permantly? TW Hello, To delete libraries permanently, you can force a bank repack. The API to repack a bank is: ...

Reterieve serverclass name using PROCESS_GETINFOLIST
Hi All, Please let me know whether anybody tried retriving serverclass name using PROCESS_GETINFOLIST_ I see that in the manual it is mentioned that we need to pass token # 101 to retreive the serverclass name but later in the manual it is mentioned that 101 attribute token does not return anything and it should not be used. Has anybody used it then please let me know. Also let me know is there any other API we can use to retrieve the server class name ? I mean when process comes up, we will get its process handle and using the process handle we need to identify its serverclass name. Manual...

perl regex: stopping short when using .*
I have a string such as: blah blah <asl*&23fcLK> blah blah the < and > will always be on the end, IF they are present at all, and anything can be in between them. How can I capture everything in between without the < and > if they even exist? I tried variations of: [<\s]+(.*(?=>)?)\s+ and also tried using [^>] in place of (?=>) and [<\s]+(.*)[<\s]+ but everytime i use the .* it always picks up the > symbol at the end, and I can get it to stop there, but i cannot get it to exclude the > So, how can I tell it to "get everything, including ...

Bind error : Address already in use problem
In my program: The parent process acts as a server The child (forked process) acts as a client When I try to bind a socket to a port and IP of my machine I am getting Address already in use error. I have tried different port numbers too but that was of no avail. Can someone help ! I used 2004,2006,3000, 5005 ports. Ankur On 2=D4=C216=C8=D5, =C9=CF=CE=E710=CA=B136=B7=D6, "ankur" <ankur.a.agar...@= gmail.com> wrote: > In my program: > > The parent process acts as a server > The child (forked process) acts as a client > When I try to bind a socket to a port and...

RE: deleting table from dropped dbspace
2 issues with that. 1 - The ontape -r -D never got to the point of asking about applying llogs. (after 13 hours of running) 2 - Rolling forward the llogs would again delete the data from the tables in question.=20 Thanks, Randy -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Superboer Sent: Monday, December 11, 2006 8:03 AM To: informix-list@iiug.org Subject: Re: deleting table from dropped dbspace You have to rollforward your trx log when restoreing using ontape -r -D... see manual. this will bring you b...

Selecting records base on another table
I have a repair database with two tables not related. One contains all of the invoices for repairs based on truck number invoice number and date. The other table contains the driver name truck number and the date range that driver drove that truck. I have added a new field to the invoice table called driver. I need to attach the invoices to a driver base on the truck they drove and the date of the invoice. I am totally stumped as to how to proceed. Any help would be greatly appreciated. Thanks in advance. ...

Problem using the 'lecroy_basic_driver'!
when I use the 'lecroy_basic_driver.mdd' to communicate with lecroy waverunner 6051A osilloscope,it always returns an error that there is no 'driverData' property for device objects.Can you tell me how this mistake happens? Thanks! detail: interfaceObj = instrfind('Type', 'tcpip', 'RemoteHost', '192.168.1.5', 'RemotePort', 1861, 'Tag', ''); if isempty(interfaceObj) interfaceObj = tcpip('192.168.1.5', 1861); else fclose(interfaceObj); interfaceObj = interfaceObj(1); end deviceObj = icdevice(&...

Subject: Use of new and delete in C++
Hi, I am working on a way to create a "window" within a dos console. I'm using Microsoft Visual C++. My main problem is that I am using new to create a new CHAR_INFO array within the constructor, and I want to destroy it within the destructor using delete. However, the array is not known outside of the constructor. This is a class within a class. Until the object is created, it has no way to know the size of the array; the size is passed as a parameter to the constructor. Is there a way to make the destructor aware of the array, or to make it public to the class...

An error when using "comtool".Who can help me? #3
Hi everyone. I want to use a COM in C++ Builder programming.The COM is generated by "comtool" in Matlab7.01.Unfortunately I encounter a question. I have set the compiler using "mbuild -setup" and chose C++ Builder 6.0.And used "mexbuild -setup" to choose C++ Builder 6.0,too. In order to simplify our problem,I use a simple function: function [c]=myadd(a,b) c=add(a,b); I use "comtool" to generate the COM.The classname was "test". An error occured when building the COM object: Error Dialog: Java exception occured: java.lang. ArrayIndexOutOfBo...

Slide show: this should be fairly straightforward
Note: I tried cross-posting this message to several newsgoups, including comp.lang.perl.misc, c.l.p.moderated, comp.infosystems.www.authoring.cgi, comp.lang.javascript and comp.lang.php. Nothing appeared on my news server, so I'm trying again - this time posting a separate copy of the message to each group. I'm thinking this should be fairly easy to accomplish - a quick and dirty ... what? ... script? program? Background: I have a website - created using a html-generator application called Dreamweaver. I have some limited knowledge of html, and I sort of know what php does. I&...

Disabling onclick (using Spry)
I want to disable the onclick for a <div>. In onclick I am using Spry.Effect.AppearFade(...). Any suggestions on how to disable onclick after the first initial click from the user? "Steve" <s.r.grace@gmail.com> wrote in message news:1166735034.184924.33220@42g2000cwt.googlegroups.com... >I want to disable the onclick for a <div>. In onclick I am using > Spry.Effect.AppearFade(...). Any suggestions on how to disable onclick > after the first initial click from the user? If you were somehow reloading the page you could use onclick="return false;"...

[ace-users] TAO Installation problem in Solaris 10 using "configure".
This is a multi-part message in MIME format. ------=_NextPart_000_015F_01C79617.1A813CC0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Subject: Installation problem in Solaris 10: When I try to build our software using the new version of ACE / TAO after a proper compilation, I face the missing include file. ACE VERSION: 5.5.8 HOST MACHINE and OPERATING SYSTEM: Sun Sparc / Solaris 10 TARGET MACHINE and OPERATING SYSTEM, if different from HOST: COMPILER NAME AND VERSION (AND PATCHLEVEL): T...

Sorting problem when using macrovariable
I use pc sas. Why when I issue proc sort; by descending q&i; run; I get an error, "by variables are not properly sorted on ...." Thanks as always. A sample of the log is below: 87 %MACRO toto; 88 %DO I=1 %TO 6; 89 90 data x;set one;by id; 91 ***next 2 lines create a weight for each item to each student; 92 q1=sum(q1a*3,q1b*2,q1c*1); q2=sum(q2a*3,q2b*2,q2c*1);q3=sum(q3a*3,q3b*2,q3c*1); 93 q4=sum(q4a*3,q4b*2,q4c*1);q5=sum(q5a*3,q5b*2,q5c*1);q6=sum(q6a*3,q6b*2,q6c*1); 94 IF NAME=" " THEN DELETE; 95 run; 96 proc sort ; BY descending q&...

Using orderby with multiple fields
I should be able to figure this one out, but it's late: I'm trying to sort a report by two fields. I can do it easily if I hard-code the fields. However, I want to get user input and store the input in variables, which would then be used in the OrderBy method. Right now it looks like this and is not working: rptMember.OrderBy = strfield1, strfield2 What am I doing wrong? Any help would be greatly appreciated. Thank you, tim For a report, anything in its Sorting And Grouping dialog will override the OrderBy property. You can reassign the ControlSource of the GroupLevel, as s...

ARX Discrete Time Model using Discrete Filter
I'm trying to model a furnace based on a 4th-order ARX model using the Discrete Filter (in Control-&gt;Simulation-&gt;Discrete) to input the coefficients (ai, bi).&nbsp; I've used Excel to estimate the coefficients and the fit is more than acceptable.&nbsp; However, using the Discrete Filter as a transfer function doesn't provide the correct response to a step change.&nbsp; Am I using the correct function?&nbsp; As far as I know the exponents on all "z", both numerator and denominator, are negative in the ARX model and the Discrete Transfer Functio...

Kernel deletion
Forgive a novice question, but is there some neat way to delete an unwanted kernel? I have Core 3 Fedora and recently did an Up2date which netted me a new kernel. It seems to run fine and I'd like to get rid of the previous one. Do I have to delete individual files in the /boot directory and from the grub.conf file? -- Ron hi Ronald Hands wrote: > Forgive a novice question, but is there some neat way to delete an > unwanted kernel? I have Core 3 Fedora and recently did an Up2date which > netted me a new kernel. It seems to run fine and I'd like to get rid o...

How to force drop table?
I'm trying to delete a table with db.execute DROP TABLE tempTable I've tried first clearing the RecordSource of the forms that use tempTable like this: Me!RecordSource = "" But it does not seem to help. I still get this error: Error Number 3211: The database engine could not lock table 'tempTable' because it is already in use by another person or process. Is there a way to force drop this table? Or kill whatever has a lock on it? No quick answers to the question you're asking, but an alternative suggestion: If you're regularly creating ...

Use of rvalue reference in the given scenario
Hello. Given class Sprite { /*...*/ }; class Toon : public Sprite { /*...*/ }; , then doing the following Sprite& s1 = Toon {}; is invalid since the result of the right-hand side expression is an rvalue, which can't be bound to a non-const reference. Sure enough, making it const like const Sprite& s1 = Toon {}; takes care of the problem. Now, suppose you actually want to keep the reference as non-const, in order to be able to invoke non-const members, would it be OK to use an rvalue reference? Sprite&& s1 = Toon {}; I've been us...

Hermws
I have been meaning to ask for a long time what Hermes is doing while the message in the title above is displayed in the 'Hermes transfer status' window, but no activity is shown on the router. During this time the RiscPC single-tasks. Hermes v2.65 is in use. Normally the delay in resuming control is short and does not matter, but recenty the delay was several minutes after someone had sent me an email with a very large attachment, Brian. -- ______________________________________________________________ Brian Carroll, Ripon, North Yorkshire, UK __________________...

Re: Which to use, SAS or SUDAAN?
> From: PingHe9@gmail.com > SAS's survey procedures (proc surveyfreq and proc > surveylogistic) can pretty much accomplish the same tasks that were > used to be done by SUDAAN (proc crosstab and proc rlogist). When > comparing the results, you may find they provide the same point > estimates, but slightly different standard errors. SUDAAN has been the > software used in most published NHANES-related articles including > those published in recent years. I would appreciate your opinion as > which software to use as I am preparing a menuscript using NHANES > ...

How to obtain the process ID of the current IDL process in a platform-independent way?
*** Question Is there a platform-independent "IDL way" to obtain the process ID of the current IDL process? *** Background I need the process ID (PID) of the current IDL process. Currently I have a working solution for a specific platform (Solaris 9 and 10), specifically IDLUnix> pid = CALL_EXTERNAL("/lib/sparcv9/libc.so", 'getpid') The reliance on a library from the operating system library limits the applicability to that particular platform and installation, so I consider it only a provisional solution. A recent discovery is the Unix libidl.so library th...

calculation percentages using sql command
Hi I have a table with the following attributes: Timestamp and a value. I would like to calculate the percentage of how many values occuring above a figurative threshold value between the hours of 6 and 10 AM? Is there a function available for doing this in a sql statement? Thanks Mahesh Mahesh S wrote: > Hi > > I have a table with the following attributes: Timestamp and a value. > > I would like to calculate the percentage of how many values occuring > above a figurative threshold value between the hours of 6 and 10 AM? > > Is there a funct...