Select Distinct Records 1

[alexanderthegreat]

I am trying to select only the distinct monthyear from my table but I have to order it by eventdate DESC

SELECT DISTINCT TOP 100 PERCENT monthyear
FROM dbo.newsservices
ORDER BY eventdate DESC

But this what happens:

monthyear eventdate
May 2004 5/10/2004
April 2004 4/15/2004
March 2004 3/30/2004
March 2004 3/17/2004
March 2004 3/9/2004
March 2004 3/1/2004
February 2004 2/22/2004
February 2004 2/9/2004

Whereas I need just one month of March to show for example.

 

[vongrunt]

> But this what happens:

Not possible - any attempt to ORDER BY anything outside DISTINCT list results with runtime error.

Try MIN() or MAX() query instead.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[alexanderthegreat]

So say something like;

SELECT MIN (monthyear), eventdate
FROM dbo.newsservices
ORDER BY eventdate DESC

 

[vongrunt]

SELECT monthyear, MIN(eventdate) AS firstDate
FROM dbo.newsservices
ORDER BY firstDate DESC

This returns distinct monthyear values and smallest eventdate value for each.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

Aww... Sunday afternoon bug:

SELECT monthyear, MIN(eventdate) AS firstDate
FROM dbo.newsservices
GROUP BY monthyear
ORDER BY firstDate DESC

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[alexanderthegreat]

Yes but even this query

SELECT DISTINCT MIN(DISTINCT monthyear) AS Expr1, eventdate
FROM dbo.newsservices
GROUP BY eventdate

Returns dupe values in monthyear
February 2004 2/9/2004
February 2004 2/22/2004
March 2004 3/1/2004
March 2004 3/9/2004
March 2004 3/17/2004
March 2004 3/30/2004
April 2004 4/15/2004
May 2004 5/10/2004

 

[alexanderthegreat]

Nice VonGrunt!