how to select the two largest dates from a column where impala sql-CodePudding

I have the following column

 extract_dt
------------
2022-02-06 |
2022-02-06 |
2022-02-06 |
2022-02-06 |
2022-02-06 |
2022-02-06 |
2022-02-06 |
2022-01-30 |
2022-01-30 |
2022-01-30 |
2022-01-30 |
2022-01-23 |
2022-01-23 |
2022-01-23 |
2022-01-23 |
2022-01-23 |
2022-01-23 |

I wanted to get only the data that has the two most recent dates, automatically, since every week a new extraction is performed and new dates are entered in the column.

I tried to use it, but without success.

SELECT distinct top(2) extract_dt
FROM table
ORDER BY extract_dt desc

and got the following error

AnalysisException: Syntax error in line 263: where extract_dt = SELECT distinct top(2) extract_dt ^ Encountered: SELECT Expected: CASE, CAST, EXISTS, FALSE, IF, INTERVAL, NOT, NULL, TRUNCATE, TRUE, IDENTIFIER CAUSED BY: Exception: syntax error

and I also tried

SELECT MAX(extract_dt)
FROM table
WHERE extract_dt NOT IN (SELECT Max (extract_dt)
                           FROM table);

returning the following error

AnalysisException: Syntax error in line 263: SELECT MAX(extract_dt) ^ Encountered: SELECT Expected: AND, AS, ASC, BETWEEN, CROSS, DESC, DIV, ELSE, END, FOLLOWING, FROM, FULL, GROUP, IGNORE, HAVING, ILIKE , IN, INNER, IREGEXP, IS, JOIN, LEFT, LIKE, LIMIT, LOCATION, NOT, NULLS, OFFSET, ON, OR, ORDER, PRECEDING, RANGE, REGEXP, RIGHT, RLIKE, ROWS, SET, STRAIGHT_JOIN, THEN, UNION , USING, WHEN, WHERE, COMMA, IDENTIFIER CAUSED BY: Exception: Syntax error

CodePudding user response：

You can use limit:

select distinct extract_dt
from table
order by extract_dt desc
limit 2

CodePudding user response：

Another solution can be User-defined functions. In the first round, you will select

SELECT Max (extract_dt) 
FROM table INTO variable

Then SELECT AND compare with that value in another SELECT.