Find earliest and latest dates of specified records from a table using SQL

Question

Hi

I have a table (in MS SQL 2005) with a selection of dates. I want to be able to apply a WHERE statement to return a group of them and then return which date is the earliest from one column and which one is the latest from another column. Here is an example table:

ID StartDate  EndDate    Person
1  01/03/2010 03/03/2010 Paul
2  12/05/2010 22/05/2010 Steve
3  04/03/2101 08/03/2010 Paul

So I want to return all the records where Person = 'Paul'. But return something like (earliest ) StartDate = 01/03/2010 (from record ID 1) and (latest) EndDate = 08/03/2010 (from record ID 3).

Thanks in advance

Answer 1

You need the min and max aggregate functions, e.g. a very simple case:

select min(StartDate), max(EndDate)
from data
where Person = 'Paul'

You have all the usual power of SQL, so selection from a sub-query is available.

Answer 2

It would be neat to use a group by as well. So If you like all the persons in your result, and you leave out the where clause, you wouldn't get erroneous data:

select person, min(StartDate), max(EndDate)
from data
group by person

or

select person, min(StartDate), max(EndDate)
from data
where person ='Paul'
group by person

or

select person, min(StartDate), max(EndDate)
from data
group by person
having person ='Paul'