仅选择满足指定条件的组的前两行

Question

我的表结构：

ID   Name   Code
----------------
124  John   ABC
456  Jake   YQA
857  James  ABC
745  Jones  IUJ
878  Jared  YQA
989  Jimmy  HIJ
900  Jess   ABC
988  Jessi  YQA

诸如此类。。。。。。

我想选择前两行代码为ABC和YQA

我需要如下输出：

ID   Name   Code
----------------
124  John   ABC
857  James  ABC
456  Jake   YQA
878  Jared  YQA

简单地说，请，因为我是的新手

Answer 1

使用ROW_NUMBER()，并按照ID的顺序为ABC和YQA代码选择最早的2条记录。

WITH cte AS (
SELECT t.*, ROW_NUMBER() OVER (PARTITITON BY Code ORDER BY ID) rn
FROM yourTable t
WHERE Code IN ('ABC', 'YQA')
)
SELECT ID, Name, Code
FROM cte
WHERE rn <= 2
ORDER BY Code, Id;

Answer 2

使用行号

with t as 
(select *, 
row_number()  over (partition by survey) as idx 
from table 
where code in ('ABC','YQA')) 
select id, name, code from t where idx < 3;