CODEWITHSUNDEEP
sqlpostgresql
user504909
user504909

Reputation: 9549

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   |   12       |     WK      |  table
4   |    15      |      WK     | test
...

I want to get the 20 rows in which all identifier should be continued and status should be 'AV'? How to do it?

I need the identifier is continued like 1,2,3,4,5,6...., so identifier like 1,3,4,5 will not be selected.

I expect result is:

id   | identifier |  status  
any  |   101      |    AV
any  |   102      |    AV
any  |   103      |    AV
...
any  |   120      |    AV

Upvotes: 0

Views: 236

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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

select t.*
from (select t.*,
             lag(identifier, 19) over (partition by status order by identifier) as prev19_status_identifier
      from t
     ) t
where prev19_status_identifer = identifier - 19 and
      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 (partition by status, identifier - seqnum) as cnt
      from (select t.*,
                   row_number() over (partition by status order by identifier) as seqnum
            from t
            where status = 'AV'
           ) t
     ) t
where cnt >= 20;

Upvotes: 0

Indra Prakash Tiwari
Indra Prakash Tiwari

Reputation: 1057

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

Upvotes: 0

user15375082
user15375082

Reputation:

Your query should be same as this one:

select * 
from tableA 
where status = 'AV'
order by identifier
limit 20

order by is using asc as default so you don't need to set asc for this query.

Upvotes: 0

tmaj
tmaj

Reputation: 34987

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

SELECT * FROM tableA 
WHERE status = 'AV'
ORDER BY 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'
ORDER BY Numbers.N
LIMIT 20

Fiddle here

For

CREATE TABLE TABLE1(
   ID INT PRIMARY KEY     NOT NULL,
   IDENTIFIER     INT     NOT NULL,
   NAME           TEXT    NOT NULL,
   STATUS           TEXT    NOT NULL
   
);
INSERT INTO TABLE1 VALUES(11123, 1,  'A', 'AV');
INSERT INTO TABLE1 VALUES(22312, 2,  'B', 'ZB');
INSERT INTO TABLE1 VALUES(1323, 3, 'C', 'AV');

the result is

n   id  identifier  name    status
1   11123   1   A   AV
2   (null)  (null)  (null)  (null)
3   1323    3   C   AV
4   (null)  (null)  (null)  (null)
5   (null)  (null)  (null)  (null)

Upvotes: 1

jhs
jhs

Reputation: 13

Use LIMIT keyword

Your query should look like this:

SELECT TN.*
FROM YouTableName AS TN
ORDER BY TN.identifier ASC   -- or DESC depending of order wanted
LIMIT 20

Upvotes: 0

Related Questions