DB2

Special DB2 Functionality

OVER()

Over defines the window that will be used to further aggregate the results.

Examples

ROW_NUMBER() OVER (PARTITION BY books."language_id" ORDER BY books."title") --> there is also an order by language in descending order

ROWNUMBER name title 1 Spanish Don Quixote 1 Russian The Brothers Karamazov 2 Russian War and Peace 1 Italian The Divine Comedy This partitions the results to order first by language then by title. The OVER window that is created is handled last, so the ordering on language is done first, then it does the OVER() partition and ordering. The rownumber starts at 1 for each language, so in this case there are 3 languages.

MONTHNAME

DAYNAME

DAYOFYEAR

DAYS

HOUR

DAY

MINUTE

YEAR

MONTH

SECOND

CURRENT_DATE