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,
