SQL query to limit number of rows having distinct values

Is there a way in SQL to use a query that is equivalent to the following:

select * from table1, table2 where some_join_condition
and some_other_condition and count(distinct(table1.id)) < some_number;

Let us say table1 is an employee table. Then a join will cause data about a single employee to be spread across开发者_JS百科 multiple rows. I want to limit the number of distinct employees returned to some number. A condition on row number or something similar will not be sufficient in this case.

So what is the best way to get the same effect the same output as intended by the above query?

select *
from (select * from employee where rownum < some_number and some_id_filter), table2
where some_join_condition and some_other_condition;

This will work for nearly all DBs

SELECT * 
FROM table1 t1
INNER JOIN  table2 t2
ON some_join_condition
   AND some_other_condition 
INNER JOIN ( 
    SELECT t1.id
    FROM table1 t1
    HAVING 
        count(t1.ID) > someNumber
    ) on t1.id = t1.id

Some DBs have special syntax to make this a little bit eaiser.

I may not have a full understanding of what you're trying to accomplish, but lets say you're trying to get it down to 1 row per employee, but each join is causing multiple rows per employee and grouping by employee name and other fields is still not unique enough to get it down to a single row, then you can try using ranking and partitioning and then select the rank you prefer for each employee partition.

See example : http://msdn.microsoft.com/en-us/library/ms176102.aspx