March 25, 2004 at 1:00 pm
I'm having some problems trying to query a table that is set up like so:
| NIGO | |||
| ID | INTERNALID | EVENT_DATE | OTHER COLUMNS... |
| 1 | 363636 | 1/20/2004 | ... |
| 2 | 898989 | 1/21/2004 | ... |
| 3 | 363636 | 1/26/2004 | ... |
| 4 | 898989 | 1/22/2004 | ... |
| 5 | 363636 | 1/31/2004 | ... |
| 6 | 898989 | 1/29/2004 | ... |
The ID column is an Identity field. InternalID relates a group of records. The other column is there just as a place holder. There are actually 7 additional fields to the table, but are not relevant to the solution.
One thing I need to do is to find the first record for each group of internalid, including all the other fields. The only way to be certain is to find the min(event_date) on internalid. This can be done with:
select internalid, min(event_date) as event_date from nigo group by internalid
However i'm not sure how to incorporate that into the rest of the solution. Somehow I need to get the unique ID for each of those records, and then join that to the full table.
March 25, 2004 at 5:18 pm
select * from nigo a,
(select internalid, min(event_date) as event_date from nigo group by internalid) b
where a.internalid = b.internalid and a.event_date = b.event_date
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply