SQL Server query to select local maximums

I have this data. I need to get the lowest $ full rows for each person.

Amount Date    Name
$123   Jun 1   Peter
$120   Jun 5   Peter
$123   Jun 5   Paul
$100   Jun 1   Paul
$220   Jun 3   Paul

The result of the SQl Server query should be:

$开发者_运维问答120   Jun 5   Peter
$100   Jun 1   Paul

SQL Server 2005+ Version

;WITH CTE AS
(
    SELECT
        Amount, [Date], Name,
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY [Amount]) AS RowNum
    FROM Table
)
SELECT *
FROM CTE
WHERE RowNum = 1

Alternative Version

SELECT t.Amount, t.[Date], t.Name
FROM
(
    SELECT Name, MIN(Amount) AS MinAmount
    FROM Table
    GROUP BY Name
) m
INNER JOIN Table t
    ON t.Name = m.Name
    AND t.Amount = m.Amount

One way which works on SQL Server 7 and up

select t1.* 
from(select min(amount) Minamount,name
from Yourtable
group by name) t2 
join Yourtable t1 on t1.name = t2.name
and t1.amount = t2.Minamount

There are a couple of ways to solve this, see here: Including an Aggregated Column's Related Values

SELECT * FROM TableName T1 WHERE NOT EXISTS 
    (SELECT * FROM TableName T2 
    WHERE T2.Name = T1.Name AND T2.Amount < T1.Amount)

In the event of ties, both rows will be shown in this scenario.

Group on the person to get the lowest amount for each person, then join the table to get the date for each row:

select y.Amount, y.Date, y.Name
from (
  select min(Amount), Name
  from TheTable
  group by Name
) x
inner join TheTable y on x.Name = y.Name and x.Amount = y.Amount

If the amount can exist on more than one date for a person, pick one of the dates, for example the first:

select y.Amount, min(y.Date), y.Name
from (
  select min(Amount), Name
  from TheTable
  group by Name
) x
inner join TheTable y on x.Name = y.Name and x.Amount = y.Amount
group by y.Amount, y.Name

Not quite the most efficient possible, but simpler to read:

SELECT DISTINCT [Name], [Date], MIN([Amount]) OVER(PARTITION BY [Name])
FROM #Table