SQL - Finding maximum date in set of rows along with all other data

Question

I have a table with rows like below

id, name , phName , startDate, endDate

1   A    , Name      1-1-2011  5-1-2011

2   B    , Name      5-1-2011  10-10-2011

3   C    , Name      2-1-2011  11-10-2011

4   X    , XXName    2-1-2011  11-10-2011

Now I need to find the max(startDate) along with all other columns for a given phName.

Essentially, I want the resultset to look like below for phName of "Name"

max(startDate)
5-1-2011,         1   A    , Name      1-1-2011  5-1-2011

5-1-2011,         2   B    , Name      5-1-2011  10-10-2011

5-1-2011,         3   C    , Name      2-1-2011  11-10-2011

I don't want to use temp table to get the result set.

Answer 1

SELECT 
   (SELECT Max(StartDate) FROM myTable WHERE phName = "Name") as MaxDate, 
   id, name , phName , startDate, endDate
FROM myTable
WHERE phName = "Name"

Answer 2

In Oracle you can use something like this:

select
    MAX(startDate) OVER(PARTITION BY phName),
    ID, name, phName, startDate, endDate
from table
where phName = 'Name';

Answer 3

The obvious solution is to use sub-queries but somewhat inneficient - particularly on MySQL (which does not handle push-predicates in sub-queries well).

Daniel Hilgarth's solution only works on Oracle.

For a generic, efficient solution (which unfortunately results in somewhat obfuascated code), see the max concat trick