I have a dataframe like this

I need output like

We have to print a latest year with all month data present in the date column .please refer output table.

i used SQL query like

Select Top 10 * from table where Order by (Date) DESC;

The max() select only one date so that didn't help me

But didn't get expected answer. Can please someone help me with the query ?

CodePudding user response：

You can just use MAX in a subquery, this will produce the intended outcome you have shown in your question:

SELECT yourcolumn
FROM yourtable
WHERE 
YEAR(yourcolumn) = (SELECT MAX(YEAR(yourcolumn)) FROM yourtable);

The latest year is 2022, so MAX in the subquery will find this year and the whole query will select all dates in 2022.

CodePudding user response：

SELECT *
FROM tablename
WHERE datecolumn >= (SELECT DATE_FORMAT(MAX(datecolumn), '%Y-01-01')
                     FROM tablename)

To improve this query you'd have an index by datecolumn (or where this coolumn is an expression prefix).