Hiding (or ignoring) similar database items.

[RevoltPuppy]

I have an event database with the following fields:

(primary key) id, summary, dtstart, dtend, category

The ID field is unique. The summary field is a name that isn't necessarily unique. The dtstart and dtend are the start and end dates of the events. Finally, the category is either "Event" or "Weekly".

Events are put into the database individually.

Example fields:

1 ¦ The Great Pumpkin Party ¦ 10/26/2008 1:00:00 PM ¦ 10/26/2008 6:00:00 PM ¦ Event
2 ¦ Adventure Hike ¦ 3/7/2009 2:00:00 PM ¦ NULL ¦ Weekly
3 ¦ Adventure Hike ¦ 3/14/2009 2:00:00 PM ¦ NULL ¦ Weekly
4 ¦ Adventure Hike ¦ 11/21/2009 2:00:00 PM ¦ NULL ¦ Weekly
5 ¦ Horse-Drawn Carriage Ride ¦ 3/6/2009 7:00:00 AM ¦ 3/7/2009 12:00:00 PM ¦ Weekly
6 ¦ Horse-Drawn Carriage Ride ¦ 3/13/2009 7:00:00 AM ¦ 3/14/2009 12:00:00 PM ¦ Weekly
7 ¦ Wine Lovers Getaway ¦ 3/21/2009 7:00:00 AM ¦ 3/22/2009 12:00:00 PM ¦ Event

An output ordered by dtstart roughly yields this:
The Great Pumpkin Party
Horse-Drawn Carriage Ride
Adventure Hike
Horse Drawn Carriage Ride
Adventure Hike
Adventure Hike
Wine Lovers Getaway

I would like to only display the first unique summary of each event. So only one Adventure Hike and one Horse-Drawn Carriage Ride would show up in the output. These "Weekly" events show up constantly, so I only want the latest upcoming event to be shown. "Events" are always unqiue, anyway. So basically, only one of each summary should be displayed.

Things I've tried:
*I can't use DISTINCT, because each row has a different dtstart and dtend.
*I can't use MIN(dtstart) because once a unique variable, such as the ID comes into play, that function doesn't do what I need it to do anymore.
*I can't group in ColdFusion (<cfoutput query="calendar" group="summary">) since the similar events are not always adjacent in the query output, so they don't group up.

I'm fairly green when it comes to database queries, so your help would be quite appreciated.

Here is what I am left with, which is also what I started with:

SELECT id, summary, dtstart, dtend, category
FROM the_table
ORDER BY dtstart, summary

[LifeinAsia]

Just to clarify, you're looking for the output to look like this?
The Great Pumpkin Party
Horse-Drawn Carriage Ride
Adventure Hike
Wine Lovers Getaway

If you, you'll probably need to do some checking in CF to see if the summary has already been displayed yet.

[edited by: LifeinAsia at 9:45 pm (utc) on Mar. 12, 2009]

[RevoltPuppy]

Yes, that is the goal. Do you know how I might go about this in CF?

[LifeinAsia]

Try the following:
<cfset DisplayList="">
<cfoutput query="calendar">
<cfif ListFindNoCase(DisplayList,calendar.summary) EQ 0>
#calendar.summary#<br>
<cfset DisplayList=ListAppend(DisplayList,calendar.summary)>
</cfif>
</cfoutput>

[RevoltPuppy]

Fantastic. That looks to have done the trick. I really appreciate it.

I've never used (or heard of) ListFindNoCase before, so I'll have to look into it and see exactly what it does, but for now I'm calling it good.

[LifeinAsia]

It's like ListFind (searches a list of items), but is case-insensitive.