/* Available in MySQL, SQL Server, PostgreSQL, Oracle */
SELECT
Name,
Population,
FIRST_VALUE(Population) over p_code AS first_population,
LAG(Population) over p_code AS prior_population,
LEAD(Population) over p_code AS next_population,
NTH_VALUE(Population, 3) over p_code AS third_population
FROM country
WHERE Continent = "South America"
WINDOW p_code AS (PARTITION BY Continent ORDER BY Population)
| Name | Population | first_population | prior_population | next_population | third_population |
| Falkland Islands | 2000 | 2000 | NULL | 181,000 | NULL |
| French Guiana | 181,000 | 2000 | 2000 | 417,000 | NULL |
| Suriname | 417,000 | 2000 | 181,000 | 861,000 | 417,000 |
| Guyana | 861,000 | 2000 | 417,000 | 3,337,000 | 417,000 |
| Uruguay | 3,337,000 | 2000 | 861,000 | 5,496,000 | 417,000 |
| Paraguay | 5,496,000 | 2000 | 3,337,000 | 8,329,000 | 417,000 |
| Bolivia | 8,329,000 | 2000 | 5,496,000 | 12,646,000 | 417,000 |
| Ecuador | 12,646,000 | 2000 | 8,329,000 | 15,211,000 | 417,000 |
| ... |
| LAG(expr) is a function that returns the previous column value in the sort order. LEAD(expr) is a function that returns the next column value in the sort order. FIRST_VALUE(expr) is a function that returns the first value of the column in the sort order. LAST_VALUE(expr) is a function that returns the last value of the column in the sort order. NTH_VALUE(expr, N) is a function that returns the nth column value in the sort order. |