CODEWITHSUNDEEP
sql
stack user
stack user

Reputation: 300

SQL to fetch Unique values based on condition

I have below data. The condition is that if the Id has two different types then take Long, such that there should not be any duplicate Id's

**id   type**
  1    Short          
  1    Long
  2    Short
  3    Short
  3    Long
  4    Short

And i need output like this. 

 **id   type**
   1    Long
   2    Short
   3    Long
   4    Short

Upvotes: 0

Views: 80

Answers (3)

mkRabbani
mkRabbani

Reputation: 16918

You can simply take MIN from your Type column's value using GROUP BY on ID column. No CASE or COUNT statement is required. This following script will always work if you have specific value "short" and "long" in your column Type.

SELECT ID,MIN(Type) Type
FROM your_table
GROUP BY ID

Upvotes: 1

user11982798
user11982798

Reputation: 1908

You can do this:

Select id, case when count(id)>1 then 'Long' else min(Type) End as Type
from Tbl 
group by id

Upvotes: 0

sam
sam

Reputation: 1985

Does this work for you:

select id, 
       case when count(id) > 1 then 'Long' else 'Short' end as type
from tmp 
group by id

Upvotes: 1

Related Questions