CODEWITHSUNDEEP
sqlhiveql
havmaage
havmaage

Reputation: 603

hive sql select from table with prioritized condition

I want to select name and nametype from a table called names, but prioritized on the nametype.

table names

name        nametype    address    id
simon           01       xx         1
simon           02       xx         2 
simon           03       xx         3 
karen           01       xx         4
william         03       xx         5 
william         01       xx         6
william         02       xx         7

I want to write a query that selects the name with either nametype = 01 or 02 or 03, in that sort order. I only want the row with nametype = 01, the other 2 rows should not be selected.

How can I achieve this in sql, I need to find out how to mark a name already selected.

Upvotes: 0

Views: 155

Answers (2)

havmaage
havmaage

Reputation: 603

Managed to figure it out my self using coalesce 

pseudocode:

if nametypes = OECD207
     select row
 elseif nametypes = OECD203
     select row
 elseif nametypes =OECD204
     select row
end if

OECD207 OECD203 OECD204 ... being the prioritized list

select
navn
, coalesce(OECD207, OECD203, OECD204, ...) as nametype 
from
(
select
navn
, max(case when nametype = "OECD207" then nametype else null end) as OECD207
, max(case when nametype = "OECD203" then nametype else null end) as OECD203
, max(case when nametype = "OECD204" then nametype else null end) as OECD204
...
from ...
group by
navn
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you only want rows with 01, then use where:

select t.*
from t
where t.nametype = '01';

If you want rows with the minimum value of nametype (which seems to be the intention of your question), then use window functions and where. One method uses min():

select t.*
from (select t.*, min(t.nametype) over (partition by name) as min_nametype
      from t
     ) t
where nametype = min_nametype;

Upvotes: 1

Related Questions