Selecting maximum distinct date by month

Question

Post reply

Selecting maximum distinct date by month

teterinmikhail

SSC-Addicted

Points: 477
More actions
June 2, 2009 at 2:31 pm

#132399

Hi Guys,
I need to select a maximum distinct date by month. I have table, then I run the following query on it:
select distinct yyyymmdd
from view_MT
order by yyyymmdd asc
and I get:
20090112
20090130
20090201
20090215
20090308
Now I need to extract the maximum date by month from this list, so the output should be:
20090130
20090215
20090308
How would I achieve something like that? Thank you all for your time in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

I'm not promising that this will scale, but it will give the results you require

DECLARE @table TABLE (date DATETIME)

INSERT INTO @table (

date

)

SELECT

'20090112'

UNION ALL

SELECT

'20090130'

UNION ALL

SELECT

'20090201'

UNION ALL

SELECT

'20090215'

UNION ALL

SELECT

'20090308'

SELECT

MAX(date)

FROM

@table

GROUP BY

DATEPART(MONTH, date)

Please note how I provide test data.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

teterinmikhail SSC-Addicted Points: 477 More actions · Answer 2

Jack, thank you so much for your help, that worked wonderful!

Thanks again,

Mikhail

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

Jack Corbett (6/2/2009)
I'm not promising that this will scale, but it will give the results you require
DECLARE @table TABLE (date DATETIME)
INSERT INTO @table (
date
)
SELECT
'20090112'
UNION ALL
SELECT
'20090130'
UNION ALL
SELECT
'20090201'
UNION ALL
SELECT
'20090215'
UNION ALL
SELECT
'20090308'
SELECT
MAX(date)
FROM
@table
GROUP BY
DATEPART(MONTH, date)
Please note how I provide test data.

Your query might return incorrect results if the dates are from multiple years. The following query gets around that by grouping by the first day of the month.

SELECT

MAX(date)

FROM

@table

GROUP BY

dateadd(month,datediff(month,0,date),0)

Jack Corbett SSC Guru Points: 184388 More actions · Answer 4

Thanks Michael.

That is a better solution. I did too quick a solution.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

teterinmikhail SSC-Addicted Points: 477 More actions · Answer 5

teterinmikhail

SSC-Addicted

Points: 477

June 3, 2009 at 12:28 pm

#1004221

Thanks again guys, everything is working 🙂

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 6

To be very safe, remember to group by year and month, not just by month. Otherwise you will run into problems if your data spans more than one year.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills