Query to fetch 100 distinct rows

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 - 06:33:54 CEST