SQL Guru

[Thiko]

Hi

Can anyone come up with some sql that will give me all the stories (or data) that are mentioned more than once in an index ( a table section/column, not an Oracle index). Many Thanks!

Thiko!

 

[Thiko]

select ITEM, count(ITEM) from MYTABLE where INDEX = 'xxx' group by ITEM;

should list two columns, the left being the item name and the right being the number of times it occurs.

Selecting from that list where the count value is greater than 1 should do it.

Adding a having clause selects only the ones you want. Extending the example above, we get:

select ITEM, count(ITEM) from MYTABLE where INDEX = 'xxx' group by ITEM having count(ITEM) > 1.0;

Is that what you are looking for?

The example is taken from page 222 in0 in the complete Oracle Reference by McGraw Hill.

...... Many Thanks!

Thiko!

 

[carp]

SELECT stories, count(*)
FROM index_table
GROUP BY stories
HAVING count(*) > 1;

will give you only the entries with more than one entry.