CODEWITHSUNDEEP
sqlsql-server
solarissf
solarissf

Reputation: 1277

sql return 1st day of each month in table

I have a sql table like so with two columns...

3/1/17 100

3/2/17 200

3/3/17 300

4/3/17 600

4/4/17 700

4/5/17 800

I am trying to run a query that returns the 1st day of each month in that above table, and grab the corresponding value.

results should be

3/1/17 100

4/3/17 600

then once I have these results... do something with each one. any ideas how I can get started?

Upvotes: 0

Views: 99

Answers (2)

LukStorms
LukStorms

Reputation: 29677

An alternative (SQL Server flavour):

SELECT t.*
FROM YourTable t
JOIN (
    select MIN(DateColumn) as MinimumDate
    from YourTable
    group by FORMAT(DateColumn,'yyyyMM')
) q on (t.DateColumn = q.MinimumDate)
ORDER BY t.DateColumn;

For the GROUP BY this will also be fine:

group by YEAR(DateColumn), MONTH(DateColumn)

or

group by DATEPART(YEAR,DateColumn), DATEPART(MONTH,DateColumn)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In standard SQL, you would use row_number():

select t.*
from (select t.*,
             row_number() over (partition by extract(year from dte), extract(month from dte)
                                order by dte asc) as seqnum
      from t
     ) t
where seqnum = 1;

Most databases support this functionality, but the exact functions (particularly for dates) may differ depending on the database.

Upvotes: 2

Related Questions