How do I "append" a column with a value from a table in SQL?-CodePudding

Context

I have a SQL table with the following structure:

# TableName
 ------------ ------------ 
| ColumnA    | ColumnB    |
 ------------ ------------ 
| Person A   | 2022-03-01 |
| Person B   | 2022-03-01 |
| Person C   | 2022-03-01 |
| ...        | ...        |
| Person AAA | 2022-04-12 |
| Person BBB | 2022-04-12 |
| Person CCC | 2022-04-12 |
 ------------ ------------

I would like a query that returns all people in ColumnA from N days ago (call it 5), along with the max date in ColumnB in the table. For example:

# TableName
 ------------ ------------ 
| ColumnA    | ColumnB    |
 ------------ ------------ 
| Person D   | 2022-04-15 | # This record's value for ColumnB = 2022-04-10
| Person E   | 2022-04-15 | # This record's value for ColumnB = 2022-04-10
| Person F   | 2022-04-15 | # This record's value for ColumnB = 2022-04-10
| Person G   | 2022-04-15 | # This record's value for ColumnB = 2022-04-10
 ------------ ------------

The Catch(es)

This query is being executed via Panda's read_sql(), so I can't do something like the following (must be one SELECT statement):

SET @max_date = (SELECT MAX(ColumnB) FROM TableName);

SELECT
    ColumnA,
    ColumnB,
    @max_date
FROM TableName
WHERE ColumnB = (
    SELECT
        DISTINCT
        ColumnB
    FROM TableName
    ORDER BY ColumnB DESC
    LIMIT 1
    OFFSET 4
);

Additionally, ColumnB doesn't include all days so I can't do something like this:

SELECT
    ColumnA,
    DATE_ADD(ColumnB, INTERVAL 4 DAYS) as Blah
FROM TableName
WHERE ColumnB = (
    SELECT
        DISTINCT
        ColumnB
    FROM TableName
    ORDER BY ColumnB DESC
    LIMIT 1
    OFFSET 4
);

The Question

How would I write such a query? Is there some way I can "append" the MAX(ColumnB) via a JOIN or something like that?

CodePudding user response：

A little more complex/explicit

SELECT
  a.*,
 b.MaxColumnB a
FROM TableName a
  JOIN (SELECT MAX(ColumnB) As MaxColumnB FROM TableName) b ON 1 = 1
WHERE a.ColumnB = (
    SELECT
        DISTINCT
        ColumnB
    FROM TableName
    ORDER BY ColumnB DESC
    LIMIT 1
    OFFSET 4
);

CodePudding user response：

I hope I got your question right:

SELECT 
    ColumnA,
    max(ColumnB) as ColumnB
FROM
    TableName
WHERE
    ColumnB >= NOW() - INTERVAL 5 DAYS;