CREATE TABLE #vals
([id] INT PRIMARY KEY IDENTITY(1,1)
,[name] NVARCHAR(100)
,[val] SMALLINT
,[date] DATE
);
CREATE NONCLUSTERED INDEX [IX_vals_date] ON #vals([date]);
INSERT INTO #vals([name],[val],[date])
VALUES (N'Лена', 45, '2015-07-01'),
(N'Женя', 50, '2015-07-01'),
(N'Саша', 45, '2015-07-01'),
(N'Лена', 30, '2015-07-02');
WITH vdo AS
(
SELECT v.[id], v.[name], v.[val]
,v.[date]
,ROW_NUMBER() OVER(PARTITION BY v.[name]
ORDER BY v.[date] DESC
) AS [date_order]
FROM #vals v
)
SELECT vdo.[id], vdo.[name], vdo.[val]
FROM vdo
WHERE vdo.[date_order] = 1;
DROP TABLE #vals;
upd: Упс, только заметил тег
mysql и я так понимаю там нет поддержки ни
cte, ни
windowsfunction?
ну, тогда через подзапрос:
CREATE UNIQUE INDEX [IX_vals_name_date] ON #vals([name],[date]);
SELECT v.[id], v.[name], v.[val]
FROM #vals v
INNER JOIN (
SELECT v.[name], MAX(v.[date]) AS [date_last]
FROM #vals v
GROUP BY v.[name]
) vdo
ON v.[name] = vdo.[name] AND v.[date] = vdo.[date_last];
