Re: Query to fetch 100 distinct rows

Just to make sure I understand the question

Sample Table

Field1
a
b
c
a
a
b
c
a
a
...

and you want a distinct list of elements where the element appears more than 100 times. This being the question the solution is:

select Field1 from Sample_table
group by Field1
having count(*) > 100

By using "having" rather than where, you can query count(*) or even sum(*) if the field were numeric

Hope this helps

ramsunders_at_yahoo.com (R197509) wrote in message news:<1e562f83.0110042033.18c488cf_at_posting.google.com>...
> Hi,
> I'm trying to fetch a fixed number of distinct records for a
> particular column.
> E.g. For a table X with columns col1, col2, col3, I would like to
> get 100 distinct values for col1. There are around 1000 rows in the
> table and around 300 distinct values.
> I tried using rownum to get the number of rows fetched. But it does
> not work. What happens is that rownum is evaluated on the whole set of
> records rather than the distinct ones.
> select distinct(c1) from X where rownum < 101;
> This query returns only 60 records.
>
> However, if we are to write the query as
> select * from (select distinct(c1) from X) where rownum < 101;
> The number of records returned are 100.
>
> The concern here is that in a table with millions of records,
> wouldn't this be an inefficient way to frame the query?
> Can you please advise as to how this query could be best written.
>
> Thanks.
> Regards,
> Ram.
Received on Fri Oct 05 2001 - 20:45:46 CEST