Example: select EMPLOYEENAME from EMPLOYEE where state in ('NY','CA','TX');
Is there any way to return 10 rows rows from each value in the where clause i.e 10 names from each state.
EDIT: I am using Oracle DB.
SELECT a.* FROM ( select EMPLOYEENAME , ROW_NUMBER() OVER(PARTITION BY state ORDER BY EMPLOYEENAME) as rn from EMPLOYEE where state in ('NY','CA','TX') )a WHERE rn <=10;
select * from ( select foo,bar, row_number() over(partition by baz) rn from table group by foo,bar ) where rn <= 10
The use of analytic functions such as
row_number allow partitioning based on some criteria. This will essentially override the default
rownum that oracle assigns.
If your concern is over concurrency, recall that a read will lock based on a transaction and will not block the inserts/updates.