CODEWITHSUNDEEP
sqlpostgresqlpagination
ripper17
ripper17

Reputation: 73

Get first row of all pages while also paginating (LIMIT / OFFSET)

I have a table of entries (basically: words) that can be sorted alphabetically. I know the total number of pages already so I can display something like 1 2 3 ... 19 20 with an OnClick handler to flip to new pages. If the result for a specific page has already been loaded, it will simply show it, otherwise a new SQL request is issued with OFFSET and LIMIT.

Now it would be great to have a tooltip showing the first entry on each page, when a user hovers over the link for page "3". I know I could issue a request onMouseOver, but is it possible to get this list in the very first query already?

Example table words:

+-ID-+----Word---+
+  1 + ability   +
+  2 + able      +
+  3 + about     +
+  4 + above     +
+  5 + accept    +
+  6 + according +
+  7 + account   +
+----+-----------+

Example initial query (obviously LIMIT 2 would be something like LIMIT 50 in real query)

SELECT * FROM words ORDER BY word ASC LIMIT 2 OFFSET 0

Example further query (e.g. if user clicked on page "3"):

SELECT * FROM words ORDER BY word ASC LIMIT 2 OFFSET 4

Desired result:

I'd love to have one initial query that returns ability and able (obviously) but then also returns about (first item on page 2), accept (first item on page 3) and account (first item on page 4). Again, I'm sure this would be easy with an event handler on MouseOver or something like this or also probably be not too hard with two subsequent queries, but I'd love to do it in one query if possible. It'd be best if the "first-of-other-pages" items would be named differently, but I guess that would be manageable differently as well. The initial query will always be with OFFSET 0 (i.e. the user can't start on page 12).

Currently on PostgreSQL Version 12.1

Upvotes: 0

Views: 409

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

If you want one query that returns the nth row, you can use row_number() and modulo arithmetic:

select w.*
from (select w.*, row_number() over (order by word) as seqnum
      from words w
     ) w
where w.seqnum % 50 = 1;

You may be able to do this using just id, if it is sequential, gapless, starts with 1, and represents the ordering of the words.

Upvotes: 1

Related Questions