i am not sure if what i want to do is possible, but hopefully it is, and someone out there can tell me how to do it! i will simplify the problem to just deal with the part i can't figure out.
anyways, here's a sample db setup: (dots are just for formatting - i have no idea how to make a table in this forum)
name . . . location . . . . . date
bob . . . . . usa . . . . . 2005-02-16
bob . . . . . usa . . . . . 2005-02-18
bob . . . . . usa . . . . . 2005-02-20
bob . . . . . usa . . . . . 2005-02-22
sam . . . . canada . . . 2005-02-18
sam . . . . canada . . . 2005-02-21
sam . . . . canada . . . 2005-02-24
so, what i want to do is select between a certain date range, say 2005-02-16 to 2005-02-24. the tricky part is, i only want to actually select the results with the oldest and most recent date (in the range) where all other fields are the same.
ie: the results would be:
bob . . . usa . . . 2005-02-16
bob . . . usa . . . 2005-02-22
sam . . canada . 2005-02-18
sam . . canada . 2005-02-24
anyone know if this is possible? any help on this would be appreciated.
Look up in the manual using GROUP WITH HIDDEN FIELDS they warn you that you could get unpredictable results. Any other database application would give you an error for leaving out a field in your group by that was in your select. (Except for aggregate fields).