Help trying to achieve the following

[mds1256]

hello

 

I currently have 1 table with the following columns:

 

 

id  |  name  |  date

 

 

and with the following data as an example:

 

 

1, test1, 2010-11-25

2, test2, 2010-10-21

3, test1, 2010-11-26

4, test3, 2011-01-01

 

 

and what i am wanting to do is get a select statement that selects 'name' and 'date' but where the name exists twice i need to select that 2nd date as another column, so in the ideal world i need the following from a select statement:

 

id | name  | date            | date2

1,  test1,    2010-11-25,  2010-11-26

2,  test2,    2010-10-21

4,  test3,    2011-01-01

 

[mds1256]

sorted with the following sql

 

select distinct(p.name) dname, (select min(date) from calendar where name = dname) as date1, (select max(date) from calendar where name = dname) as date2 from calendar p