this is my table:
ID/Number/Date
1/111/2021-01-01
2/111/2021-01-02
6/333/2921-01-01
I need a table which count the rows based on Number order by Date asc. This should be my final table:
ID/Number/Date/Row_No_Count
1/111/2021-01-01/1
2/111/2021-01-02/2
6/333/2921-01-01/1
How to achieve this with hive? Is their any function?
try row_number() window function like below.
select t.*,
row_number() over(partition by Number order by Number,Date asc ) as Row_No_Count
from table t
Row Number is a Function IN SQL Server for this type of Work. You can solve Your Problem on based on below Query .
Query : Select *,row_number () Over (partition by Number order by Number) 'Row_Number_Count' From t ;
Output :
id Number Date Row_Number_Count
----------- ----------- ---------- --------------------
1 111 2021-01-01 1
2 111 2021-01-02 2
6 333 2921-01-01 1
(3 rows affected)