vincentdesmares

CREATE TABLE employe AS (
  SELECT
    n                                          AS id,
    'Employe name' || n                        AS name,
    trunc(random() * 9000 + 1000)              AS salary,
    chr((65 + trunc(random() * 5)) :: INTEGER) AS category
  FROM
        generate_series(1, 5000, 1) AS n
);

vincentdesmares

  id  |       name        | salary | category
------+-------------------+--------+----------
 4023 | Employe name 4023 |   9996 | A
 3795 | Employe name 3795 |   9993 | A
 1050 | Employe name 1050 |   9990 | B
 2664 | Employe name 2664 |   9988 | B
  956 | Employe name 956  |   9996 | C
 2843 | Employe name 2843 |   9986 | C
  607 | Employe name 607  |   9976 | D
 4983 | Employe name 4983 |   9970 | D

vincentdesmares

SELECT *
FROM
  (
    (SELECT *
     FROM employe
     WHERE category = 'A'
     ORDER BY salary DESC
     LIMIT 2)
    UNION (SELECT *
           FROM employe

vincentdesmares

  id  |       name        | salary | category | row_number 
------+-------------------+--------+----------+------------
 4023 | Employe name 4023 |   9996 | A        |          1
 3795 | Employe name 3795 |   9993 | A        |          2
 1050 | Employe name 1050 |   9990 | B        |          1
 2664 | Employe name 2664 |   9988 | B        |          2
  956 | Employe name 956  |   9996 | C        |          1
 2843 | Employe name 2843 |   9986 | C        |          2
  607 | Employe name 607  |   9976 | D        |          1
 4983 | Employe name 4983 |   9970 | D        |          2

vincentdesmares

SELECT *
FROM (
       SELECT
         *,
         row_number()
         OVER ordered_report_by_category
       FROM
         employe
       WINDOW ordered_report_by_category AS (
         PARTITION BY

vincentdesmares

  id  |       name        | salary | category | count | ordered_count
------+-------------------+--------+----------+-------+---------------
 2456 | Employe name 2456 |   1008 | A        |  1035 |          1035
 2209 | Employe name 2209 |   1015 | A        |  1035 |          1035
 3692 | Employe name 3692 |   1022 | A        |  1035 |          1035
 3885 | Employe name 3885 |   1031 | A        |  1035 |          1035
  436 | Employe name 436  |   1035 | A        |  1035 |          1035

vincentdesmares

SELECT
  *,
  count(*)
  OVER report_by_category         AS count,
  count(*)
  OVER ordered_report_by_category AS ordered_count
FROM
  employe
WINDOW
    report_by_category AS (

vincentdesmares

  id  |       name        | salary | category | count | ordered_count
------+-------------------+--------+----------+-------+---------------
 2456 | Employe name 2456 |   1008 | A        |  1035 |             1
 2209 | Employe name 2209 |   1015 | A        |  1035 |             2
 3692 | Employe name 3692 |   1022 | A        |  1035 |             3
 3885 | Employe name 3885 |   1031 | A        |  1035 |             4
  436 | Employe name 436  |   1035 | A        |  1035 |             5

vincentdesmares

SELECT
  *,
  count(*)
  OVER report_by_category AS count,
  count(*)
  OVER (report_by_category
    ORDER BY salary)      AS ordered_count
FROM
  employe
WINDOW

vincentdesmares

 id  |       name        | salary | category | min  |       avg        | max
-----+-------------------+--------+----------+------+------------------+------
1003 | Employe name 1003 |   6515 | C        | 1009 | 5550.74427480916 | 9996
1005 | Employe name 1005 |   8187 | C        | 1009 | 5550.74427480916 | 9996
1001 | Employe name 1001 |   6106 | D        | 1001 | 5507.41912512716 | 9976
1002 | Employe name 1002 |   2491 | E        | 1001 | 5551.63221884498 | 9997
1004 | Employe name 1004 |   5130 | E        | 1001 | 5551.63221884498 | 9997