Consider only fist few elements when reading from a table-CodePudding

I have a situation where I would like to apply filter logic to just take first few records if the value in below table is A.

Name	Value
Test 1	A
Test 2	A
Test 3	A
Test 4	A
Test 5	A
Test 6	B
Test 7	B
Test 8	B
Test 9	B

The result I want incase if I want the value A based results to be limited by first 2 records when I select records fro, the above table is as shown below. Any idea on how to accomplish this? Should I consider window functions?

Name	Value
Test 1	A
Test 2	A
Test 6	B
Test 7	B
Test 8	B
Test 9	B

CodePudding user response：

Yes, a window function will help.

with t as
(
 select *, row_number() over (partition by "Value" order by "Name") rn
 from the_table
)
select * from t
where ("Value" = 'A' and rn <= 2) or ("Value" <> 'A');

CodePudding user response：

Try a Common Table Expression that selects rows with 'A' and limits to 2 rows, and UNION SELECT that with all rows with 'B' ...

WITH
indata(Name,Value) AS (
          SELECT 'Test 1','A'
UNION ALL SELECT 'Test 2','A'
UNION ALL SELECT 'Test 3','A'
UNION ALL SELECT 'Test 4','A'
UNION ALL SELECT 'Test 5','A'
UNION ALL SELECT 'Test 6','B'
UNION ALL SELECT 'Test 7','B'
UNION ALL SELECT 'Test 8','B'
UNION ALL SELECT 'Test 9','B'
)
,
a_rows AS (
  SELECT
    *
  FROM indata
  WHERE value='A'
  ORDER BY 1
  LIMIT 2
)
SELECT
  *
FROM a_rows
UNION ALL SELECT
  *
FROM indata
WHERE value='B'
;
-- out   Name  | Value 
-- out -------- -------
-- out  Test 1 | A
-- out  Test 2 | A
-- out  Test 6 | B
-- out  Test 7 | B
-- out  Test 8 | B
-- out  Test 9 | B