how to find multiple occurances

[raygg]

I am not very experienced in pl/sql. I need to write a script that selects only the message numbers that repeat more than 10 times.
create table message_table (
msg_date date,
trn_num number(10),
trn_hist_no number(4),
trn_sub_hist_no(4),
trn_timestamp date);
When the first 4 columns are identical I want to print the row only when there are 10 or more rows where only the timestamp differs.

Tnaks in advance.

 

[wushutwist]

Try:

select msg_date, trn_num, trn_hist_no, trn_sub_hist_no
from message_table 
group by msg_date, trn_num, trn_hist_no, trn_sub_hist_no
having Count(msg_date) > 10;

I think this will work. My SQL is kind of rusty and I didn't test it so I make no gurantees. Wushutwist

 

[raygg]

Unfortunately that does not exactly work. It groups things but does not force the 2st 4 columns to be identical, and it dows not select the value of the timestamp.

 

[carp]

Try

SELECT *
FROM message_table
WHERE (msg_date, trn_num,trn_hist_no,trn_sub_hist_no) IN
(SELECT msg_date,trn_num,trn_hist_no,trn_sub_hist_no
FROM message_table
GROUP BY msg_date,trn_num,trn_hist_no,trn_sub_hist_no
HAVING COUNT(*)>=10);

 

[raygg]

How do I get columns to print in addition to the GROUP BY without including those in the GROUP BY - to restate the problem
TABLE
COLA
COLB
COLC
COLD
COLE
If I group by COLA, COLB, COLC,
how do I get COLD and COLE to print?
Getting back to your response CARP, if there were also a COLD and COLE that I want to print once 10 consecutive rows have identical msg_date,trn_num,trn_hist_no,trn_sub_hist_no, how is that done? I cannot put them in the SELECT, because then they have to be in the GROUP BY, and I don't want them in the GROUP BY. What I need to see is the changes in COLD and COLE when all those other cols are identical.

I was experimenting with UNION but I am not sure what I am doing with that.