SQL - how to select data on a monthly basis?-CodePudding

I have the following DF :

Date	Name	NumberID
05-JAN-2022	Test	1
07-JAN-2022	Test	1
09-FEB-2022	Test	1
10-FEB-2022	Test	1
11-FEB-2022	Test	1
12-MAR-2022	Test	1
02-MAR-2022	Test	1

What would be the SQL Request which would return :

Date	Name	NumberID
05-JAN-2022	Test	1
09-FEB-2022	Test	1
02-MAR-2022	Test	1

Note that I can't do a

SELECT * FROM Tab1 tb1 WHERE tb1.Date in ('05-JAN-2022','09-FEB-2022',ect..)

Because there is a ton of dates and I don't know them.

More complex example

Date	Name	NumberID
05-JAN-2022	Test	1
07-JAN-2022	Test	1
09-FEB-2022	Test	1
10-FEB-2022	Test	1
11-FEB-2022	Test1	1
12-MAR-2022	Test	1
02-MAR-2022	Test	1

the output would be :

Date	Name	NumberID
05-JAN-2022	Test	1
09-FEB-2022	Test	1
11-FEB-2022	Test1	1
02-MAR-2022	Test	1

My Query :

SELECT 
tc.TITLETest,
MIN(tc.DATETest) AS YOURDATE,
tc.CRSTest
FROM Table tc
WHERE tc.TC= 1 
GROUP BY EXTRACT(MONTH FROM YOURDATE),tc.TITLETest,tc.CRSTest
ORDER BY YOURDATE

CodePudding user response：

We can select the earliest date with MIN and GROUP BY the month:

SELECT MIN(yourdate) AS yourdate, name, numberid
FROM yourtable
GROUP BY EXTRACT(MONTH FROM yourdate), name, numberid
ORDER BY yourdate;

Using SQL key words as table name or column name is a bad idea, so the column "date" should be renamed to something more meaningful, for example "sellDate". This query produces this result for your sample data:

Yourdate	Name	NumberID
05-JAN-2022	Test	1
09-FEB-2022	Test	1
11-FEB-2022	Test1	1
02-MAR-2022	Test	1

Try out here: db<>fiddle

CodePudding user response：

We can use ROW_NUMBER here:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY TO_CHAR("Date", 'MON-YYYY')
                                   ORDER BY "Date") rn
    FROM yourTable t
)

SELECT "Date", Name, NumberID
FROM cte
WHERE rn = 1;