当前位置: 动力学知识库 > 问答 > 编程问答 >

oracle - Sql to pick n rows from each value of where clause

问题描述:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: