question Re: 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 in Archives of Support Questions (Read Only)

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

lsundar19 — Mon, 26 Sep 2016 20:45:16 GMT

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

Re: 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

apathan — Mon, 26 Sep 2016 22:51:12 GMT

@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

Re: 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

apathan — Mon, 03 Oct 2016 13:18:10 GMT

@Sundar Lakshmanan

Is this issue resolved?

Re: 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

lsundar19 — Mon, 03 Oct 2016 17:10:15 GMT

Yess.. Thanks Ayub

Re: 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

pminovic — Mon, 03 Oct 2016 17:40:20 GMT

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