Getting the first and last value
The analytical functions that return the first or last value from an ordered list prove to be very helpful in queries. In this exercise, you will get familiar with them. The syntax is:
FIRST_VALUE(numeric_expression) OVER ([PARTITION BY column] ORDER BY column ROW_or_RANGE frame)LAST_VALUE(numeric_expression) OVER ([PARTITION BY column] ORDER BY column ROW_or_RANGE frame)
You will write a query to retrieve all the voters from Spain and the USA. Then, you will add in your query some commands for retrieving the birth date of the youngest and the oldest voter from each country. You want to see these values on each row, to be able to compare them with the birth date of each voter.
This exercise is part of the course
Functions for Manipulating Data in SQL Server
Exercise instructions
- Retrieve the birth date of the oldest voter from each country.
- Retrieve the birth date of the youngest voter from each country.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
first_name + ' ' + last_name AS name,
country,
birthdate,
-- Retrieve the birthdate of the oldest voter per country
___(birthdate)
OVER (PARTITION BY ___ ORDER BY ___) AS oldest_voter,
-- Retrieve the birthdate of the youngest voter per country
___(___)
OVER (___ country ORDER BY ___ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS youngest_voter
FROM voters
WHERE country IN ('Spain', 'USA');