Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select Distinct Records 1

Status
Not open for further replies.

alexanderthegreat

IS-IT--Management
Sep 9, 2005
70
US
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.
 
> 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]
[banghead]
 
So say something like;

SELECT MIN (monthyear), eventdate
FROM dbo.newsservices
ORDER BY eventdate DESC
 
Code:
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]
[banghead]
 
Aww... Sunday afternoon bug:
Code:
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]
[banghead]
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top