select first instance

[buzzt]

How would I select only the first instance of each result? In other words, if there was an 10 events taking place 3 times each in the date range specified, how could I get the db only to give me first instance of each distinct event?

 

[rac2]

You must store the data which determines when the event occurred. This could be a DATETIME value, or a TIMESTAMP, or a unique sequence number for each event which is generated and incremented for each new row, that is, an autonumber column. Call the column event_id or event_timestamp.

It must be that that when you say "10 events taking place 3 times each" that you mean that there are 10 different kinds of events and each kind occurs 3 times". Call the column that identifies the kind of event, event_type.

SELECT event_type,
            MIN(event_id) AS "TheOne"
FROM MyParty
WHERE event_date BETWEEN @dtBegin AND @dtEnd
GROUP BY event_type

This tells the id of the first occurance of each event type which occurred during the interval.

JOIN this subquery to the basic table using the "TheOne" as the foreign key.

SELECT event_name, event_description
FROM MyParty a 
JOIN (
SELECT event_type,
            MIN(event_id) AS "TheOne"
FROM MyParty
WHERE event_date BETWEEN @dtBegin AND @dtEnd
GROUP BY event_type
        ) b ON b.TheOne = a.event_id

You must have a unique primary key and you must have a sequential value that identifies the first thing.

 

[buzzt]

Thanks a lot!