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"