Window Function Concepts and Syntax As an example of one of those nonaggregate window functions, this query uses  ROW_NUMBER() , which produces the row number of each row within its partition. In this case, rows are numbered per country. By default, partition rows are unordered and row numbering is nondeterministic. To sort partition rows, include an  ORDER BY  clause within the window definition. The query uses unordered and ordered partitions (the row_num1  and  row_num2  columns) to illustrate the difference between omitting and including  ORDER BY : As mentioned previously, to use a window function (or treat an aggregate function as a window function), include an  OVER  clause following the function call. The  OVER  clause has two forms: Both forms define how the window function should process query rows. They differ in whether the window is defined directly in the  OVER  clause, or supplied by a reference to a named window defined elsewhere in the query: If  OVER()  is empty, the window consists of all query rows and the window function computes a result using all rows. Otherwise, the clauses present within the parentheses determine which query rows are used to compute the function result and how they are partitioned and ordered: For  OVER ( window_spec )  syntax, the window specification has several parts, all optional: 2427 • partition_clause : A  PARTITION BY  clause indicates how to divide the query rows into groups. The window function result for a given row is based on the rows of the partition that contains the row. If PARTITION BY  is omitted, there is a single partition consisting of all query rows. • window_name : The name of a window defined by a  WINDOW  clause elsewhere in the query. If window_name  appears by itself within the  OVER  clause, it completely defines the window. If partitioning, ordering, or framing clauses are also given, they modify interpretation of the named window. For details, see Section 12.21.4, “Named Windows” . •In the first case, the window specification appears directly in the  OVER  clause, between the parentheses. •In the second case,  window_name  is the name for a window specification defined by a  WINDOW  clause elsewhere in the query. For details, see Section 12.21.4, “Named Windows” . window_spec : [ window_name ] [ partition_clause ] [ order_clause ] [ frame_clause ] over_clause : {OVER ( window_spec ) | OVER  window_name } mysql>  SELECT year, country, product, profit, ROW_NUMBER() OVER(PARTITION BY country) AS row_num1, ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2 FROM sales; +------+---------+------------+--------+----------+----------+ | year | country | product | profit | row_num1 | row_num2 | +------+---------+------------+--------+----------+----------+ | 2000 | Finland | Computer | 1500 | 2 | 1 | | 2000 | Finland | Phone | 100 | 1 | 2 | | 2001 | Finland | Phone | 10 | 3 | 3 | | 2000 | India | Calculator | 75 | 2 | 1 | | 2000 | India | Calculator | 75 | 3 | 2 | | 2000 | India | Computer | 1200 | 1 | 3 | | 2000 | USA | Calculator | 75 | 5 | 1 | | 2000 | USA | Computer | 1500 | 4 | 2 | | 2001 | USA | Calculator | 50 | 2 | 3 | | 2001 | USA | Computer | 1500 | 3 | 4 | | 2001 | USA | Computer | 1200 | 7 | 5 | | 2001 | USA | TV | 150 | 1 | 6 | | 2001 | USA | TV | 100 | 6 | 7 | +------+---------+------------+--------+----------+----------+