If I decipher this right, you basically want to select all people where the row number according to the descending ID appears in the address. The final result should then be limited to certain of these row numbers.
Then you don't need to use that cumbersome LIMIT
/OFFSET
construct at all. You can simply use the row_number()
window function.
To filter for the row numbers you can simply use IN
. Depending on what you want here you can either use a list of literals, especially if the numbers aren't consecutive. Or you can use generate_series()
to generate a list of consecutive numbers. Of course you can also use a subquery, when the numbers are stored in another table.
With a list of literals that would look something like this:
SELECT pn.personid,
pn.lastname,
pn.firstname,
pn.address,
pn.city
FROM (SELECT p.personid,
p.lastname,
p.firstname,
p.address,
p.city,
row_number() OVER (ORDER BY p.personid DESC) n
FROM persons p) pn
WHERE pn.address LIKE concat('%', pn.n, '%')
AND pn.n IN (1, 2, 4);
If you want to use generate_series()
an example would be:
SELECT pn.personid,
pn.lastname,
pn.firstname,
pn.address,
pn.city
FROM (SELECT p.personid,
p.lastname,
p.firstname,
p.address,
p.city,
row_number() OVER (ORDER BY p.personid DESC) n
FROM persons p) pn
WHERE pn.address LIKE concat('%', pn.n, '%')
AND pn.n IN (SELECT s.n
FROM generate_series(1, 3) s (n));
And a subquery of another table could be used like so:
SELECT pn.personid,
pn.lastname,
pn.firstname,
pn.address,
pn.city
FROM (SELECT p.personid,
p.lastname,
p.firstname,
p.address,
p.city,
row_number() OVER (ORDER BY p.personid DESC) n
FROM persons p) pn
WHERE pn.address LIKE concat('%', pn.n, '%')
AND pn.n IN (SELECT t.nmuloc
FROM elbat t);
For larger sets of numbers you can also consider to use an INNER JOIN
on the numbers instead of IN
.
Using generate_series()
:
SELECT pn.personid,
pn.lastname,
pn.firstname,
pn.address,
pn.city
FROM (SELECT p.personid,
p.lastname,
p.firstname,
p.address,
p.city,
row_number() OVER (ORDER BY p.personid DESC) n
FROM persons p) pn
INNER JOIN generate_series(1, 1000000) s (n)
ON s.n = pn.n
WHERE pn.address LIKE concat('%', pn.n, '%');
Or when the numbers are in another table:
SELECT pn.personid,
pn.lastname,
pn.firstname,
pn.address,
pn.city
FROM (SELECT p.personid,
p.lastname,
p.firstname,
p.address,
p.city,
row_number() OVER (ORDER BY p.personid DESC) n
FROM persons p) pn
INNER JOIN elbat t
ON t.nmuloc = pn.n
WHERE pn.address LIKE concat('%', pn.n, '%');
Note that I also changed the regular expression pattern matching to a simple LIKE
. That would make the queries a bit more portable. But you can of course replace that by any expression you really need.
db<>fiddle (with some of the variants)