Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution
Highlighted

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

Contributor

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 ACCEPTED SOLUTION

Accepted Solutions

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

@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

4 REPLIES 4

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

@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

@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

Contributor

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

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

Don't have an account?
Coming from Hortonworks? Activate your account here