Re: query trouble

There are a couple of ways to find duplicates... here are some thoughts...

1. group by.

Create a query which will return the multiple rows and then form a GROUP BY clause which tests for count greater than 1 row.

select a,b,c
from TableA
Where whereclause
group by KEY having count(*)>1

select a,b,c
from TableA
Where whereclause
group by KEY having count(*)>1

Formulate a query which will return the multiple rows and then form a GROUP BY clause which tests for count greater than 1 row.

This can be combined with an outer query to get more detailed info:

select a,b,c,d,f,e,g....
from TableA
where a in
(select a from TableA {whereclause} Group by {key}
   Having count(*)>1)

select a,b,c
from TableA
Where whereclause
group by KEY having count(*)>1

Formulate a query which will return the multiple rows and then form a GROUP BY clause which tests for count greater than 1 row.

This can be combined with an outer query to get more detailed info:
select a,b,c,d,f,e,g....
from TableA
where a in
(select a from TableA {whereclause} Group by {key}
   Having count(*)>1)

2) Correlated subselect using MAX(Rowid)

Finding Duplicates can be done with a special correlated subselect. Generally is pretty fast, too.

Finding Duplicates can be done with a special correlated subselect. Generally is pretty fast, too.

The general form is:
select x
from tableA A
where SearchConditions
and ROWID != (select max(rowid)
  from tableA B
  where a.key = b.key
  and SearchConditions)

What this says is: While I am searching tablea (alias A), go and do a search on tablea (alias B) and get all the records the match key "KEY"; perform a MAX on the various rowids and return that Max value. When the alias A ROWID matches Alias B ROWID, then there the row is not a duplicate. However, if the KEY value is a duplicate, then the current alias A ROWID will NOT be equal to the alias B ROWID, and the 'x' columns will be returned.

If you wanted to then return ALL records for the duplicate users, this whole query block could be in the Where clause of another outer query which would return the specific data you wished...

If you wanted to then return ALL records for the duplicate users, this whole query block could be in the Where clause of another outer query which would return the specific data you wished...

Select a,b,c,d,e,f,g
from TableA Z
where z.KEY IN
( [write the whole query from above, where 'x' above

      returns the KEY] )

3) Your specific problem.
Your specific case would read something like....

I will use it as written. Then, your specific case would read something like....

SELECT
  a.UNAME, a.LONG_DATE, a.OUT_DATE, a.SESS_LENGTH, a.CALLED_FROM into
  :UNAME, :LONG_DATE, :OUT_DATE, :SESS_LENGTH, :CALLED_FROM from MAIN.RADIUS_LOGS A
where
( (TO_DATE(a.LONG_DATE, 'dy mon dd HH24:MI:SS YYYY'))
    BETWEEN (SYSDATE-1) AND SYSDATE
  )
and a.UNAME in
(select b.UNAME

   From MAIN.RADIUS_LOGS B
   where
    ( (TO_DATE(a.LONG_DATE, 'dy mon dd HH24:MI:SS YYYY'))       BETWEEN (SYSDATE-1) AND SYSDATE
    ) and ROWID != (

      (Select MAX(rowid) From MAIN.RADIUS_LOGS C
       Where b.UNAME = c.UNAME
       and ( to_date(c.Long_Date, 'dy mon dd HH24:MI:SS YYYY')
             BETWEEN
                ( to_date (b.LONG_DATE, 'dy mon dd HH24:MI:SS YYYY'))
             AND
               ( to_date(b.OUT_DATE, 'dy mon dd HH24:MI:SS YYYY'))
           )
       )

What this says is: Get all the UNAMEs (alias B) from now through Yesterday, and check to see if UNAMES existed (alias C) whose logon (long date) time was between the Long Date and Out Date of the UNAME (alias B), and return those UNAMEs to outer Query (Alias A) so that it can find ALL the data for UNAMEs in this list during now through yesterday.

It looks complicated, but it is really a matter of layers: one layer gets all the detail, another layer answers the question of who as duplicate logins.

ACKNOWLEDGEMENT: I would like to mention that my mentor Aramazd Davidian from Glendale taught me this rowid concept. It has been useful many times in the past, and executes rather fast, and I am indebted to his kindness.

ACKNOWLEDGEMENT: I would like to mention that my mentor Aramazd Davidian from Glendale taught me this rowid concept. It has been useful many times in the past, and executes rather fast, and I am indebted to his kindess.

Good Luck,
You can call me or write if you have more questions.

Bob Proffitt
Beckman/Coulter
Brea, CA
RTProffitt_at_beckman.com
(714) 993-8426

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue May 04 1999 - 23:39:36 CEST