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) |
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'
WHERE. Same for all other window functions. It is your expectations that are wrong, not the result of the function or its documentation.
OVERclause? What were you expecting here?LEADis calculated afterWHERE.HAVING name = 'French'to perform the filter after getting the result set.