[SOLVED] MySQL Lead function returns NULL

MySQL Lead function returns NULL

I have this MySQL table:

id	Name
1	Spanish
2	French
3	German

I am using this statement:

SELECT id, Name, Lead(Name) OVER (ORDER BY id) AS 'next' from MyTable WHERE Name='French'

The expected result is this:

id	Name	next
2	French	German

The result is this:

id	Name	next
2	French	(NULL)

Solution

I'm not an SQL expert, but this works:

With e AS (
    SELECT id, Name, Lead(Name) OVER (ORDER BY id) AS 'next' from MyTable 
) SELECT Id, Name, next FROM e WHERE Name='French'

There may be more efficient solutions, but since the table is about languages I don't expect it to contain millions of records. Therefore I think performance will hardly be an issue.

/Edit: This code does the same and I suppose that it will be indeed more efficient for large tables since it will not first calculate all 'next' fields for the entire table:

SELECT d.*, e.Next
    FROM MyTable d OUTER APPLY
    (SELECT TOP(1) Name AS Next
    FROM Mytable e 
    WHERE e.Id > d.Id
    ORDER BY Id
    ) AS e 
    WHERE d.Name = 'French'