CODEWITHSUNDEEP
sql
Jainam Desai
Jainam Desai

Reputation: 69

I need to select a column with distinct value from sql

I am given a table which consists a column of various departments and another column consisting of various dhdempno. I need to select a unique dhdempno which represents the department head. A particular department has multiple employees with same dhdmempno and one unique dhdempno which represents the department head. What query shall i execute in order to obtain the result ?

Example

Department - dhdempno
abc           1  
abc           1 
abc           2  
asd           5  
asd           5
asd           6

The query should return 

abc    2
asd    6

Upvotes: 3

Views: 90

Answers (6)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use below query:

select s.Department, max(s.dhdempno)as dhdempno from 
 (
   SELECT DISTINCT DEPARTMENT, dhdempno FROM DUMMY
 ) s group by s.Department

Upvotes: 0

PRABA
PRABA

Reputation: 460

select [Department],[dhdempno] from [dbo].[Your_Table_Name] where [dhdempno] in (select [dhdempno] from [dbo].[Your_Table_Name] group by [dhdempno] having count(*) = 1)

Upvotes: 0

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

SELECT DepartmentId, MAX(dhdempno)
FROM [Table-name]
GROUP BY DepartmentId

Upvotes: 0

Deepak Sharma
Deepak Sharma

Reputation: 419

enter image description hereUse Query like

SELECT DEPARTMENT,DHDEMPNO FROM DEPARTMENT
GROUP BY DEPARTMENT,DHDEMPNO
HAVING COUNT(DHDEMPNO)=1

will give you record with unique dhdempono in respective department

Upvotes: 0

Aditya Pansare
Aditya Pansare

Reputation: 1132

Here is the query which will give result you are seeking.

select Department, dhdempno
from (
    SELECT Department, dhdempno, count(dhdempno) as counts
    FROM <your_table_name>
    group by Department, dhdempno ) as sub
where counts = 1;

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

Try this

select departmentid, count(distinct dhdempno) as counting from your_table
group by departmentid

Upvotes: 1

Related Questions