complex sql query

[lrempel]

hello,

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)

tbl_info:

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.

thanks.

[djones]

Not sure about how to produce the format you want with min and max dates as separate rows, but you could use a GROUP BY clause in your query to get the min and max date for each name:

SELECT name, location, MIN(date), MAX(date)
FROM tbl_info
WHERE date BETWEEN '2005-02-16' AND '2005-02-24'
GROUP BY name;

would give this:

+------+----------+------------+------------+
| name | location | MIN(date) | MAX(date) |
+------+----------+------------+------------+
| bob | usa | 2005-02-16 | 2005-02-22 |
| sam | canada | 2005-02-18 | 2005-02-24 |
+------+----------+------------+------------+

Hope this helps.

[djones]

Sorry, I misread your criteria - if the location is also significant then you should change the GROUP BY clause to include it:

...GROUP BY name, location;

[lrempel]

that works well enough for my purposes. thanks a lot!

[guelphdad]

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).