How to build a sql-query with the output of actual data by day?

Question

MySQL

There is a table
id | name | val | date
Sample data
1 | Lena | 45 | 2015-07-01
2 | Zhenya | 50 | 2015-07-01
3 | Sasha | 45 | 2015-07-01
4 | Lena | 30 | 2015-07-02
You need to select name and val relevant (for the current day) if there is no data for the current day, then for the previous one.
That is:
Zhenya | 50
Sasha | 45
Lena | 30
I just can't handle the query, it works like this
SELECT name, val FROM table GROUP BY name;
But only when the records are sequential (fresh first, then old)
Surprisingly, ORDER BY date DESC doesn't help.

Answer 1

Sorry, I didn't read the question carefully the first time.

select
  tmp.name,
  test.val
from
  ( 
    select 
      name, 
      max(date) as max_date
    from test
    group by name
  ) as tmp
join test on test.name=tmp.name and test.date=tmp.max_date

Must have an index by name and date

Answer 2

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: Oops, I just noticed the mysql tag and I understand there is no support for either cte or windowsfunction ?
well, then through a subquery:

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];

Answer 3

Try something like this SELECT name, val FROM table where convert([YourDateName], 102)
= convert(getdate(), 102)
And then group by, order by... =)