filter the data from records in sql

Question

I am having a one table. in which, there are 12 records available. in those i want to filter and get data like from hp cell value source column to before hp cell value source column records.

Here is the table :

seq_id       file_name      source       date

b21345350    a.txt          ad          2022-04-15
b32145660    e.txt          qe          2022-04-15
c43526890    ace.txt        hp          2022-04-15
re2345566    wer.csv        hp          2022-04-15
b43251044    op.xlsx        fa          2022-04-15
b6512400     ip.csv         jm          2022-04-15
b9123420     tb.xlsx        tp          2022-04-15
b3214563     cv.txt         ux          2022-04-14
b45678900    em.txt         hp          2022-04-14
b65357023    rt.csv         hp          2022-04-14
b90879081    ty.txt         mp          2022-04-14
b19019019    sd.txt         jp          2022-04-14

Here it should be output result :

 seq_id       file_name      source       date

c43526890    ace.txt        hp          2022-04-15
re2345566    wer.csv        hp          2022-04-15
b43251044    op.xlsx        fa          2022-04-15
b6512400     ip.csv         jm          2022-04-15
b9123420     tb.xlsx        tp          2022-04-15
b3214563     cv.txt         ux          2022-04-14

Answer 1

There is no natural order of rows in a relational database (like there is in a spreadsheet). See:

• Select last n rows without use of order by clause
• Deterministic sort order for window functions

Assuming an id column to establish the order of rows that you seem to take for granted (and is not), this would work:

SELECT (t).*
FROM  (
   SELECT t, count(*) FILTER (WHERE source = 'hp') OVER (ORDER BY id) AS hp_ct
   FROM tbl t
   ) sub
WHERE hp_ct BETWEEN 1 AND 2;

db<>fiddle here

You seem to want all rows starting with the first instance of source = 'hp' and stopping at the 3rd instance of the same. So do a running count in the subquery sub, and then filter all rows with count 1 & 2.

I work with a table alias t to grab the whole row and decompose in the outer SELECT to get rid of the added hp_ct without spelling out all column names. That's optional.