Window Function Row_number()

The ORDER BY expression in the OVER clause determines the number. Each value is ordered within its partition. Rows with equal values for the ORDER BY expressions receive different row numbers non-deterministically.

Query

select *,row_number() over (order by age) as rownumber from
   dfs.`/Users/../workspace/Drill-samples/student_list.json`;

Result

+------+-------+-----+--------+-----------+--------+-------+-------+--------------------+---------+------------+
|  ID  | name  | age | gender | standard  |  mark1 | mark2 | mark3 |      addr          | pincode | rownumber  |
+------+-------+-----+--------+-----------+--------+-------+-------+--------------------+---------+------------+
|  001 | Adam  | 12  |  male  |   six     |   70   |  50   |  60   |  23 new street     | 111222  |     1      |
|  002 | Amit  | 12  |  male  |   six     |   40   |  50   |  40   |  12 old street     | 111222  |     2      |
|  003 | Bob   | 12  |  male  |   six     |   60   |  80   |  70   |  10 cross street   | 111222  |     3      |
|  004 | David | 12  |  male  |   six     |   50   |  70   |  70   |  15 express avenue | 111222  |     4      | 
|  005 | Esha  | 12  | female |   six     |   70   |  60   |  65   |  20 garden street  | 111222  |     5      |
|  006 | Ganga | 12  | female |   six     |   100  |  95   |  98   |  25 north street   | 111222  |     6      |
|  008 | Leena | 12  | female |   six     |   90   |  85   |  95   |  24 south street   | 111222  |     7      |
|  007 | Jack  | 13  |  male  |   six     |   55   |  45   |  45   |  2 park street     | 111222  |     8      |
|  009 | Mary  | 13  | female |   six     |   75   |  85   |  90   |  5 west street     | 111222  |     9      |
|  010 | Peter | 13  | female |   six     |   80   |  85   |  88   |  16 park avenue    | 111222  |     10     |
+------+-------+-----+--------+-----------+--------+-------+-------+--------------------+---------+------------+

Kickstart Your Career

Get certified by completing the course

Get Started