SQL Server query to select local maximums

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

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