Re: How to write this query without using subquery in the FROM clause?


Hi,

Perhaps it is not much elegant (or ugly innefficient) but this could
also work

select count(distinct a1.al_ccode)
from airline a1, airline a2
where a1.al_ccode = a2.al_ccode
and a1.something <> a2.something

'something' should be some field (or set of fields) that the same rows
from an airline does not have in common. At least must be something
not in common or they are exactly the same (exact duplicate) row.

Carlos

On 22 jun, 09:31, "Roy Hann" <specia...@xxxxxxxxxxxxxxxxxxxxx> wrote:
<tianfeic...@xxxxxxxxx> wrote in message

news:1182480791.197422.34050@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



I asked a similar question can be seen here:
http://groups.google.com.au/group/comp.databases.ingres/browse_thread...
I learned that "The ability to use a subquery in the FROM clause was
introduced in Ingres 2006 Release 2".

---

My new question is about finding number of duplicated rows in a table.
Here I'm using demodb comes with Ingres 2006 R2 as an example.

Table airline has 42 rows. To find out which values in column al_ccode
are duplicated, I can simply write:
select al_ccode from airline group by al_ccode having
count(al_ccode) > 1
This would return 14 rows such as:
AU
CA
DE
...
They are values that appears more than once in the table.

However sometimes all I want is number of duplication, not a list of
duplication values. I want an interger value 14 instead of 14 rows. In
many other RDBMS and Ingres 2006 R2, I would embed it into another
select count query like this:
select count(*) from (
select al_ccode from airline group by al_ccode having
count(al_ccode) > 1) as row_count

However it does not work in older version Ingres server. So my program
has to use the first way and get all rows, then calculated number of
returned rows. The problem occurs when I tried to iterate all table
and columns - it take a few hours to finish.

I did try to rewrite it using subselect satements in a where or having
clause like this:
select count(a1.al_ccode)
from airline a1
where 1 < (select count(a2.al_ccode)
from airline a2
where a2.al_ccode = a1.al_ccode)
It returns value 38, which is the total number of duplication, not
what I ask for.

Can anyone help?

I would have expected the following to work:

select count(distinct al_ccode)
from airline
where al_ccode in ( select al_ccode from airline group by al_ccode
having count(al_ccode) > 1 )

Unfortunately not only is it dog slow on my machine, it also produces an
incorrect result (it ignores the "distinct" when it computes the count). I
used a beta release of Ingres 2006 Release 2 Microsoft Windows Version II
9.1.0 (int.w32/119) for this.

I am actually pretty confident it will work correctly in an older more
mature release, but I don't have access to anything but my laptop at the
moment.

Roy


.

Relevant Pages