Version 2: How to write a postgres sql query that returns M different values ​​of a specific column with returned M + unknown record amounts?

Question

This is a slightly more complicated version of what I posted earlier. I do not want to edit the original in order to ruin the excellent answer to the question I had at that time.

Suppose a table named tau.

tau:
A | B | C
----------    
2 - 1 - red
3 - 1 - rod
4 - 1 - rope
6 - 5 - red
7 - 5 - rap
8 - 5 - rod
9 - 10 -rod
11- 10 -road
12- 13 -rud

Column A is the primary key. It will be unique. Column B is a foreign key. In my database, no integer key ever changes in tables. Column C is not a key.

This table will have many rows. There are many other columns, such as, for example, column C, which are indexed for search.

I want exactly M different values ​​from column B. Let's say M = 2

, B ( !), , B.

select distinct B From tau Where C like 'r_d' AND B < 13 Order By B Desc Limit 2

B
-----
10
5

. :

BC (C like "r_d" AND B < 13), A, B, , 2 B.

, where, true C.

, , .

A | B
------
9 - 10
8 - 5
6 - 5

, , M, 2 . , , M B. Postgresql?

Answer 1

DENSE_RANK , . SQL Fiddle. M WHERE R.rnk <= 2 2.

, M B , A , . , :

SELECT *
FROM tau
WHERE C LIKE 'r_d' AND B < 13

, . M B. DENSE_RANK ( ) B, .

SELECT
    A, B
FROM
    (
        SELECT
            A
            ,B
            ,C
            ,DENSE_RANK() OVER(ORDER BY B DESC) AS rnk
        FROM tau
        WHERE C LIKE 'r_d' AND B < 13
    ) AS R
WHERE R.rnk <= 2
ORDER BY B DESC, A DESC;

A    B
9    10
8    5
6    5

LATERAL JOIN

SQL Fiddle . M B. B. B, , B . , B , .

WITH
CTE
AS
(
    select distinct B 
    From tau 
    Where C like 'r_d' AND B < 13 
    Order By B Desc 
    Limit 2
)
SELECT
  T.A, T.B, T.C
FROM
  CTE
  INNER JOIN LATERAL
  (
      SELECT tau.A, tau.B, tau.C
      FROM tau
      WHERE tau.B = CTE.B AND tau.C like 'r_d' AND tau.B < 13 
  ) AS T ON true
ORDER BY T.B DESC, T.A DESC;