Skip to content Skip to sidebar Skip to footer

How To Fetch 20 Continue Number Row In Postgresql

I have database like: id | identifier | status | content --------------------------------------- 1 | 10 | AV | text 2 | 11 | AV | book 3 |

Solution 1:

If there are rows for identifiers 1..20 then I think you may be looking for:

SELECT * FROM tableA 
WHERE status = 'AV'ORDERBY identifier
LIMIT 20

If there are missing identifiers you could left/right join with a sequence of numbers:

SELECT Numbers.N, Table1.* FROM Table1 
RIGHT JOIN (SELECT N FROM generate_series(1, 20) N) as Numbers
ON Table1.identifier = Numbers.N AND Status = 'AV'ORDERBY Numbers.N
LIMIT 20

Fiddle here

For

CREATETABLE TABLE1(
   ID INTPRIMARY KEY     NOTNULL,
   IDENTIFIER     INTNOTNULL,
   NAME           TEXT    NOTNULL,
   STATUS           TEXT    NOTNULL
   
);
INSERTINTO TABLE1 VALUES(11123, 1,  'A', 'AV');
INSERTINTO TABLE1 VALUES(22312, 2,  'B', 'ZB');
INSERTINTO TABLE1 VALUES(1323, 3, 'C', 'AV');

the result is

n   id  identifier  name    status
1111231A   AV
2   (null)  (null)  (null)  (null)
313233   C   AV
4   (null)  (null)  (null)  (null)
5   (null)  (null)  (null)  (null)

Solution 2:

Use LIMIT keyword

Your query should look like this:

SELECT TN.*
FROM YouTableName AS TN
ORDERBY TN.identifier ASC   -- or DESC depending oforder wanted
LIMIT 20

Solution 3:

select*from (
Select T1.*from Tablename T1
innerjoin tablename T2 on (T1.identifier) = (T2.identifier -1)    
where status ='AV'unionSelect T2.*from Tablename T1
innerjoin tablename T2 on (T1.identifier) = (T2.identifier -1)  
where status ='AV'
) Res limit 20

Solution 4:

You can use lag() to get the 20th pretty easily:

select t.*from (select t.*,
             lag(identifier, 19) over (partitionby status orderby identifier) as prev19_status_identifier
      from t
     ) t
where prev19_status_identifer = identifier -19and
      status ='AV';

If you want all such rows, you can treat this as a gaps-and-islands, using:

select t.*from (select t.*,
             count(*) over (partitionby status, identifier - seqnum) as cnt
      from (select t.*,
                   row_number() over (partitionby status orderby identifier) as seqnum
            from t
            where status ='AV'
           ) t
     ) t
where cnt >=20;

Post a Comment for "How To Fetch 20 Continue Number Row In Postgresql"