Is there any chance that I use a window function to use a limit offset parameters in order to have a result as a page ?

i.e I want to limit/offset by depname to show only 2 department, but all the rows where this department appears :

  depname  | empno | salary |          avg        
  -------- |------ | ------ |----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
 tech      |     9 |   4500 | 5020.0000000000000000
 tech      |     8 |   6000 | 5020.0000000000000000
 tech      |    10 |   5200 | 5020.0000000000000000

I expect as result those :

limit : 2 - offset : 0

  depname  | empno | salary |          avg        
  -------- |------ | ------ |----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000

limit : 2 - offset : 1

  depname  | empno | salary |          avg        
  -------- |------ | ------ |----------------------
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
 tech      |     9 |   4500 | 5020.0000000000000000
 tech      |     8 |   6000 | 5020.0000000000000000
 tech      |    10 |   5200 | 5020.0000000000000000

Best I can do with Row number is this

  depname  | empno | salary |          avg          |
  -------- |------ | ------ |---------------------- |
 develop   |    11 |   5200 | 5020.0000000000000000 | 1
 develop   |     7 |   4200 | 5020.0000000000000000 | 2
 personnel |     5 |   3500 | 3700.0000000000000000 | 1
 personnel |     2 |   3900 | 3700.0000000000000000 | 2
 sales     |     3 |   4800 | 4866.6666666666666667 | 1
 sales     |     1 |   5000 | 4866.6666666666666667 | 2
 sales     |     4 |   4800 | 4866.6666666666666667 | 3
 tech      |     9 |   4500 | 5020.0000000000000000 | 1
 tech      |     8 |   6000 | 5020.0000000000000000 | 2
 tech      |    10 |   5200 | 5020.0000000000000000 | 3

So I can't limit and offset properly... Have you any idea how I can use window functions (or anything else but those seem to be great to group like this) to limit and offset on a group of value according to a certain column value.

CodePudding user response：

With DENSE_RANK() window function:

SELECT *, DENSE_RANK() OVER (ORDER BY depname) dr
FROM tablename

you get a new column dr which ranks each row by the column depname:

You can use that new column to apply the limit and offset that you want:

WITH cte AS (
  SELECT *, DENSE_RANK() OVER (ORDER BY depname) dr
  FROM tablename
)
SELECT depname, empno, salary, avg
FROM cte
WHERE dr <= 2; -- limit : 2 - offset : 0

or:

WITH cte AS (
  SELECT *, DENSE_RANK() OVER (ORDER BY depname) dr
  FROM tablename
)
SELECT depname, empno, salary, avg
FROM cte
WHERE dr > 1 AND dr <= 3; -- limit : 2 - offset : 1

See the demo.