help with my query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

help with my query

Author

Topic

alain
Starting Member

3 Posts

Posted - 2005-10-27 : 02:40:20

can anyone help me
I have a table with events (Eventdate,comedianID, Eventtime..)
I am trying to retrieve the unique comedianID from the Events table
but order by EventDate
I have tried this
Select Distinct Comedians.*
From Comedians
Where ComedianID IN(Select Top 500 ComedianID From Events where Convert(varChar(12),EventDate,101) >= Convert(varChar(12),GetDate(), 101) And EventTypeID !=4 Order by Events.EventDate Desc)
the result is fine but it does not order by eventDate

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 02:46:57

You'll need to choose which event date you want - will the most recent do?


SELECT ComedianID, MAX(EventDate) AS MAX_EventDate
FROM
(
    Select Top 500 ComedianID, EventDate
    From Events 
    where Convert(varChar(12),EventDate,101) >= Convert(varChar(12),GetDate(), 101) 
          And EventTypeID !=4 
    Order by Events.EventDate Desc
) AS X
GROUP BY ComedianID

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-27 : 02:49:33

Is Comedians table have Evendate .. since you can order by only those columns where are there in your select statement.

So you can join it with the Comedians table and put the EventDate there and then use order by EvenDate.

i guess that would work for you.

Complicated things can be done by simple thinking

alain
Starting Member

3 Posts

Posted - 2005-10-27 : 11:18:26

Thank you for the help but your solution did not work
since this query group by ComedianID sql server returns the comedianID ordered by comedianID and not the comedianIDs based on the orderdate

quote:

Originally posted by Kristen

You'll need to choose which event date you want - will the most recent do?


SELECT ComedianID, MAX(EventDate) AS MAX_EventDate
FROM
(
    Select Top 500 ComedianID, EventDate
    From Events 
    where Convert(varChar(12),EventDate,101) >= Convert(varChar(12),GetDate(), 101) 
          And EventTypeID !=4 
    Order by Events.EventDate Desc
) AS X
GROUP BY ComedianID

Kristen

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 13:43:40

Add ORDER BY MAX_EventDate ??

Kristen

alain
Starting Member

3 Posts

Posted - 2005-10-27 : 15:16:07

Thanks you so much that did work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources