| Duplicates in sql query [message #19100] |
Thu, 28 February 2002 06:04  |
James
Messages: 120
Registered: June 2000
|
Senior Member |
|
|
Hello All,
I am using Oracle 8i and I am doing a simple join to select all the records for a specific date, grouping them by the same text information field ie duplicates. I know how to select the duplicates, but I want to return all the rows from both tables and I'm not sure how to do that. I couldn't find any examples so far. The sql I have retrieves one row and I need all of the rows returned.
SQL:
select eh.text, count(*)
from events e, event_hist eh
where e.num = eh.num
and eh.time > '18-JULY-01'
and eh.time < '20-JULY-01'
group by eh.text
having count(*)>1;
Thanks,
James
|
|
|
|
| Re: Duplicates in sql query [message #19116 is a reply to message #19100] |
Thu, 28 February 2002 13:38  |
raji
Messages: 30
Registered: February 2002
|
Member |
|
|
|
your question is not clear,you need only the duplicate records,isn't that right?"but I want to return all the rows from both tables"what do you mean by this?explain.
|
|
|
|
| Re: Duplicates in sql query [message #19126 is a reply to message #19116] |
Fri, 01 March 2002 03:00 |
James
Messages: 120
Registered: June 2000
|
Senior Member |
|
|
Yes, sorry I didn't make that clearer. I want to return all the column data from all the duplicate rows. Right now I get the correct counts and text fields, but I don't have it in the query to get the other data in the other fields within the duplicate rows that I want. (ie a datetime, id number, etc...)
What I am retrieving are records with the same telephone number (text field) that contain different web page names that are being hit via that telephone number. I am trying to work out some metrics on the data. Therefore, there will be several records in the table with the same telephone number and date, but with different pagename fields. That is what I am trying to return, along with the other data.
Thanks,
James
|
|
|
|
| Re: Duplicates in sql query [message #19139 is a reply to message #19100] |
Fri, 01 March 2002 15:36 |
raji
Messages: 30
Registered: February 2002
|
Member |
|
|
Try this.
select *(columns you need) from events e,event_hist eh
where e.num = eh.num
and eh.time > '18-JULY-01'
and eh.time < '20-JULY-01'
and eh.text in (select b.text
from event_hist b
group by b.text
having count(*)>1);
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
