Search code examples
sqlhivemapreducecaseflags

Creating a flag for names having atleast 1 travel through air mode (1) as Flyer

I have following sort of data:

Names air mode
abc 0
abc 1
xyz 0
xyz 0

I want to create a flag in following fashion in HIVE:

Column A Flyer
abc 1
xyz 0

Can someone tell me how to write SQL query for this? I tried case when but subquery doesn't work within case when in hive.

I retrieved distinct names having air mode as 1, but there are 1000s of rows so I can't put them in case when statement directly as case when name in(...) then 1 else 0 end as flyer.

Solution

  • Creating a flag for names having atleast 1 travel through air mode (1) as Flyer

    You can do it using GROUP BY and MAX function

    SELECT Names, MAX(air_mode) as Flyer
FROM your_table
GROUP BY Names;

    Output :

    Names Flyer
    abc 1
    xyz 0