[SOLVED] SELECT QUERY

[eightFX]

Hello everyone,

 

I am looking for a little select query help. What I am looking to do is create a list of events with the most recent date. What I am looking for is this:

 

(2007-04-02) - The progress of the project

(2007-03-29) - Birthday party for Bob

(2007-03-17) - St. Patrick's Day Celebration

 

What I have in the table is this:

 

EventID | EventName | EventDescription | EventDate

 

1 | Project1 | The progress of the project | 2007-03-01

2 | Stpatricks | St. Patrick's Day Celebration | 2007-03-17

3 | Bob| Birthday party for Bob | 2007-03-28

4 | Bob| Birthday party for Bob | 2007-03-29

5 | Bob| Birthday party for Bob | 2007-03-29

6 | Project1 | The progress of the project | 2007-04-01

7 | Project1 | The progress of the project | 2007-04-02

 

What I had for a query that was working until I wanted to incorporate the date is this:

 

$query = SELECT DISTINCT EventName, EventDescription FROM EventTable ORDER BY EventName

 

Current output:

 

1. Birthday party for Bob

2. The progress of the project

3. St. Patrick's Day Celebration

 

 

So as I stated, the above query works for just displaying a list of the event descriptions but now I want to add the most recent date to the list as shown at the top, I tried adding the date to distinct but obviously that didn't work. I just can't seem how to get this query to have both... Any help much appreciated. Thanks!

 

[gluck]

If you want to arrange by date: use order by date then eventname -> order by date,eventname

 

If you need current date comparision use curdate() in where clause and compare the dates.

[eightFX]

The problem is that the event date can be different throughout. Therefore when I do that it shows the event multiple times which I do not want to happen in the list. I wish it were as easy as sorting by date. As for the current date comparison I am not sure that is what I am looking to do either. The more I look at this I am thinking I will need to do two queries but if someone can confirm that and state what I would need the two queries for that would be great.

[Onle]

So you really need to Select the EventDate as well.  You are wanting the Most recent date?

 

So Take Project1 You want to show 2007-04-02 and ignore the other 2 dates. Is that correct?

[gluck]

The problem is that the event date can be different throughout. Therefore when I do that it shows the event multiple times which I do not want to happen in the list. I wish it were as easy as sorting by date. As for the current date comparison I am not sure that is what I am looking to do either. The more I look at this I am thinking I will need to do two queries but if someone can confirm that and state what I would need the two queries for that would be great.

 

use distinct or group them by date or event whatever you key is

[artacus]

If EventDescription is always the same within an EventName group use:

SELECT EventName, EventDescription, MAX(EventDate) AS EventDate
FROM EventTable GROUP BY EventName

 

If not use:

SELECT
FROM EventTable AS et
JOIN (
    SELECT EventName,  MAX(EventDate) AS maxDate
    FROM EventTable GROUP BY EventName
) AS sub ON et.eventName = sub.EventName AND et.EventDate = sub.maxDate

[eightFX]

If EventDescription is always the same within an EventName group use:

SELECT EventName, EventDescription, MAX(EventDate) AS EventDate
FROM EventTable GROUP BY EventName

 

If not use:

SELECT
FROM EventTable AS et
JOIN (
    SELECT EventName,  MAX(EventDate) AS maxDate
    FROM EventTable GROUP BY EventName
) AS sub ON et.eventName = sub.EventName AND et.EventDate = sub.maxDate

 

THANKS ARTACUS! That is exactly what I was looking to do! Thank you again! Kudos!