To Whomsoever It May Concern, I've got a table that contains a LocationId field. In some cases, where a record shares the same foreign key, the LocationId might come through as -1.
What I want to do is in my select query is in the case of this happening, the previous location. Example data: Record FK StartTime EndTime Location1 110 2011/01/01 12.30 2011/01/01 6.10 4562 110 2011/01/01 3.40 2011/01/01 4.00 -13 110 2011/01/02 1.00 2011/01/02 8.00 8914 110 2011/01/02 5.00 2011/01/02 6.00 -15 110 2011/01/02 6.10 2011/01/02 6.30 -1 The -1 should come out as 456 for record 2, and 891 for record 4 and 5 Can someone help me do this wit Hive syntax? I can do it using SQL syntax (as below) but since Hive doesnt support subqueries in select clauses I am unable to get it. SELECT T1.record, T1.fk, T1.start_time, T1.end_time, CASE WHEN T1.location != -1 THEN Location ELSE ( SELECT TOP (1) T2.location FROM #temp1 AS T2 WHERE T2.record < T1.record AND T2.fk = T1.fk AND T2.location != -1 ORDER BY T2.Record DESC ) ENDFROM #temp1 AS T1 Thank you for your help in advance! On Fri, Sep 12, 2014 at 11:33 AM, Viral Parikh <viral.j.par...@gmail.com> wrote: > To Whomsoever It May Concern, > > I've got a table that contains a LocationId field. In some cases, where a > record shares the same foreign key, the LocationId might come through as -1. > > What I want to do is in my select query is in the case of this happening, > the previous location. > > Example data: > > Record FK StartTime EndTime Location1 110 > 2011/01/01 12.30 2011/01/01 6.10 4562 110 2011/01/01 3.40 > 2011/01/01 4.00 -13 110 2011/01/02 1.00 > 2011/01/02 8.00 8914 110 2011/01/02 5.00 2011/01/02 6.00 > -15 110 2011/01/02 6.10 2011/01/02 6.30 -1 > > The -1 should come out as 456 for record 2, and 891 for record 4 and 5 > > Can someone help me do this wit Hive syntax? > > I can do it using SQL syntax (as below) but since Hive doesnt support > subqueries in select clauses I am unable to get it. > > SELECT T1.record, > T1.fk, > T1.start_time, > T1.end_time, > CASE WHEN T1.location != -1 THEN Location > ELSE > ( > SELECT TOP (1) > T2.location > FROM #temp1 AS T2 > WHERE T2.record < T1.record > AND T2.fk = T1.fk > AND T2.location != -1 > ORDER BY T2.Record DESC > ) > ENDFROM #temp1 AS T1 > > Thank you for your help in advance! >