How to get the row number for particular values from hive table: For example i have column name with VAX_NUM and one of the values for this column is 0006756423. I want know the row number for this value. Do the needful. Thanks

[#1]

How to get the row number for particular values from hive table: For example i have column name with VAX_NUM and one of the values for this column is 0006756423. I want know the row number for this value. Do the needful. Thanks

[#2 · Accepted solution]

@Sundar Lakshmanan

I guess you can use something like

SELECT vax_num, rowid FROM (SELECT vax_num, ROW_NUMBER() OVER() AS rowid FROM <table_name>) <temporary_table_name> WHERE vax_num=0006756423

To give an example for the above:

SELECT id, rowid FROM (SELECT id, row_number() over() AS rowid FROM id_table) id_table_temp WHERE id=17;
INFO  : Session is already open
INFO  : Dag name: select id, rowid FROM (select id,...id=17(Stage-1)
INFO  :


INFO  : Status: Running (Executing on YARN cluster with App id application_1474453714086_0008)


INFO  : Map 1: 0/1	Reducer 2: 0/2
INFO  : Map 1: 0(+1)/1	Reducer 2: 0/2
INFO  : Map 1: 0(+1)/1	Reducer 2: 0/2
INFO  : Map 1: 0(+1)/1	Reducer 2: 0/2
INFO  : Map 1: 1/1	Reducer 2: 0/2
INFO  : Map 1: 1/1	Reducer 2: 0(+1)/1
INFO  : Map 1: 1/1	Reducer 2: 1/1
+------+--------+--+
| id  | rowid  |
+------+--------+--+
| 17   | 5      |
+------+--------+--+
1 row selected (12.202 seconds)

Please refer to: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics and Apache Hive Cookbook

[#3]

@Sundar Lakshmanan

Is this issue resolved?

[#4]

Yess.. Thanks Ayub

[#5]

@Sundar Lakshmanan If "Yess", can you please accept Ayub's answer to help us manage answered questions. Tnx!