Distinct Records from WHERE Clause

[cwsstins]

I am trying to figure out how to get distinct records from a query similar to the following (snippet from a larger query):

INSERT INTO tbl_01
(SELECT * FROM tbl_02 t2 WHERE col_02 IN
 (SELECT DISTINCT col_02 FROM tbl_02 WHERE col_01 = t2.col_01
  )
 )

If I run the SELECT DISTINCT statement alone, it indeed does bring back distinct records. But when I nest it within the SELECT * statement, I'm still getting duplicates. I understand why...there are multiple col_02 records that are IN the distinct selection of col_02 records. But how can I get around it?

 

[SantaMufasa]

Why not just say:

INSERT INTO tbl_01
(SELECT distinct * FROM tbl_02 t2);

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cwsstins]

I can't have a distinct on all columns because 1)there is a CLOB, 2)some rows will be duplicates but will not appear to be so because of the "seq" column. The data looks something like this:

callid seq calldate calltime entry
12345 1 04/06/07 11:01:23 CLOB1...
12345 2 04/06/07 11:02:12 CLOB2...
12345 3 04/07/07 09:45:50 CLOB3...
12345 4 04/06/07 11:01:23 CLOB1...

So in this example, I would want to see "seq" records 1,2,3, but not 4.

In my original code, col_02 would be calldate||calltime.

 

[SantaMufasa]

Stinsman,

You can do a DISTINCT that includes one or more CLOB columns, but not in Oracle 8i . (Why are you not at least on Oracle 9i?)

Let's take a step back and have you simply restate (non-technically) what DISTINCT values you wish to INSERT into your table. If you do that, I'm certain we can come up with the appropriate code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cwsstins]

Planning on upgrading, but not there yet.
OK, I have 4 tables where I have duplicate data. However, the records themselves are not duplicate, technically. This is data that has been transferred from another application into my application. Due to a bug, sometimes the source records are generated in the destination (my app) multiple times. But each time, the record is generated with a new col_1 for each table.

col_2 is the source data's col_1. So their col_1 becomes my col_2. My col_1 is generated dynamically. I have come up with a way to look at the tbl3.MAX(col_1) where COUNT(col_2)>1. No problem there. So I can delete anything that is not the MAX record.

But before I delete, I need to make sure all my tbl_4 records are "attached" to the MAX(col_1) record from tbl_3. Since tbl_4 has duplicate data that is not technically duplicated (because of the seq column), I was hoping to use a DISTINCT SELECT of calldate||calltime to identify the records that should be kept (or rather INSERTED from a temp_tbl_4 after being deleted from tbl_4).



tbl_1 tbl_2 tbl_3 tbl_4
col_1 col_1 col_1 col_2 col_1 col_2 seq calldate calltime
12345 12345 12345 0123 12345 0123 1 04/06/07 11:00:00
12346 12346 12346 0123 12346 0123 2 04/06/07 11:05:15
12347 12347 12347 0123 12347 0123 3 04/06/07 11:00:00

Now that I've totally confused you, I'm sure you're sorry you asked.

 

[cwsstins]

So in the example data, I would only want to keep the first two records...

 

[SantaMufasa]

Stinsman,

I'm happy to assist...I'm sure we can come up with a solution...Problem is, I'm just trying to get out the door for a 3-hour drive to my daughter's wedding Open House. Tomorrow is her reception back here. So I'll be glad to look at this again either in between the events, or after the final event.

But in the meantime, if there is someone else that is "Oracular" (as Tharg aptly puts it), that can address this issue, please do, so that Stinsman can get on with his work.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cwsstins]

Thanks Santa...much appreciated. Have fun.

 

[Turkbear]

Hi,
Maybe something along the lines of:

Insert into tbl_01(col1,col2,calldate,calltime...) select distinct col1,col2,calldate,calltime,... from tbl_02

In other words, skip the sequence column to make the data selected distinct..

If you need the sequence # in the target table, use an update statement after the insert...






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cwsstins]

Guys, thanks for your input...a colleague of mine figured out a solution.

 

[Turkbear]

Hi,
Be nice, post it if you can...

We like happy endings...






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cwsstins]

Sorry, yes here is the final product. He ended up using another set of date/time fields (from the destination recod, rather than the source record) as the unique identifier:

INSERT INTO tlb_4
(
SELECT j1.*
FROM tbl_3 3, tlb_4 j1
WHERE 3.callid = j1.callid
AND 3.tsqcallid = j1.tsqcallid
AND 3.callid IN
(
SELECT callid
FROM tbl_3
WHERE
tsqCallID IN
(
SELECT tsqCallID
FROM tbl_3
WHERE tsqCallID IS NOT NULL
GROUP BY tsqCallID HAVING COUNT(*) > 1
)
)
AND j1.entrydate||j1.entrytime IN
(
select MAX(entrydate||entrytime)
from tlb_4 j2
where j2.callid = j1.callid
and j2.tsqcallid = j1.tsqcallid
and j2.tsqdate = j1.tsqdate
and j2.tsqtime = j1.tsqtime
)
)