CODEWITHSUNDEEP
mysql
Alan Smith
Alan Smith

Reputation: 71

Select Distinct but with column's value

Here is what I want.

Let's say we have this table

Name  Email          Department             Branch
------------------------------------------------------
John  [email protected]   Information Technology SAP
John  [email protected]   Not Available          Not Available
Bret  [email protected]  Emergency Response     911
Bret  [email protected]  Not Available          Not Available
diana [email protected]  Not Available          Not Available

Now what I basically want is to get the following results:

If John has "Information Technology" and "Not Available" it will make "Information Technology" a priority. Basically it will only show Not Available row if John doesn't belong to any other department. so the result table will be like this: 

Name  Email         Department             Branch
-----------------------------------------------------
John  [email protected]  Information Technology SAP
Bret  [email protected] Emergency Response     911
Diana [email protected] Not Available          Not Available

Thanks!

Upvotes: 0

Views: 36

Answers (2)

forpas
forpas

Reputation: 164099

For this sample data you need a CASE expression to check for 'Not Available' and consider it as NULL so you can aggregate (with MIN or MAX): 

select name, email,
  coalesce(
    max(case when department = 'Not Available' then null else department end),
    'Not Available'
  ) department,
  coalesce(
    max(case when branch = 'Not Available' then null else branch end),
    'Not Available'
  ) branch
from tablename
group by name, email

See the demo.
Results:

| name  | email         | department             | branch        |
| ----- | ------------- | ---------------------- | ------------- |
| Bret  | [email protected] | Emergency Response     | 911           |
| diana | [email protected] | Not Available          | Not Available |
| John  | [email protected]  | Information Technology | SAP           |

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,name VARCHAR(12) NOT NULL  
,email VARCHAR(25) NOT NULL
,department VARCHAR(50)  NULL
,branch VARCHAR(50) NULL
);

INSERT INTO my_table VALUES
(1,'John','[email protected]','Information Technology','SAP'),
(2,'John','[email protected]',NULL,NULL),
(3,'Bret','[email protected]','Emergency Response','911'),
(4,'Bret','[email protected]',NULL,NULL),
(5,'diana','[email protected]',NULL,NULL);

SELECT name
     , email
     , MAX(department) department
     , MAX(branch) branch 
  FROM my_table 
 GROUP  
    BY name
     , email;
+-------+---------------+------------------------+--------+
| name  | email         | department             | branch |
+-------+---------------+------------------------+--------+
| Bret  | [email protected] | Emergency Response     | 911    |
| diana | [email protected] | NULL                   | NULL   |
| John  | [email protected]  | Information Technology | SAP    |
+-------+---------------+------------------------+--------+

Upvotes: 1

Related Questions