Online for a SQL statement-CodePudding

I have one with large scale, a few has nearly 10 years of data every day, I want to get each year on the last day of every month orders information,
Excuse me, great god, how do you write the SQL statement, thank you,

Assuming that the original data is as follows:
Order date order quantity
The 2018-01-01 655
The 2018-01-02 1120
The 2018-01-31 2150
The 2019-09-01 3000
The 2019-09-02 4100
The 2019-09-30 5600
The 2020-02-01 7000
The 2020-02-02 6200
The 2020-02-29 8100
The 2020-10-31 11200

I need to get the following data:
Order date order quantity
The 2018-01-31 2150
The 2019-09-30 5600
The 2020-02-29 8100
The 2020-10-31 11200

CodePudding user response:

 
SELECT * 
FROM the TABLE A 
WHERE NOT the EXISTS (SELECT 1 FROM the TABLE WHERE YEAR (a. order date)=YEAR (order date) AND MONTH (a. order date)=MONTH (order date) AND DAY (order date) & gt; DAY (a. order date)) 

- or 

SELECT * 
The FROM 
(SELECT *, ROW_NUMBER () OVER (PARTITION BY YEAR (ORDER date), MONTH (ORDER date) ORDER BY DAY (ORDER date) DESC) AS SEQ FROM TABLE) AS A 
WHERE SEQ=1