CODEWITHSUNDEEP
mysql
Akash Sourav Nayak
Akash Sourav Nayak

Reputation: 203

Query to fetch Unique value of a column and dulplicate values of other column

This is my table and i want unique value but all the realted record to that value.

         Original Table                           output
        ---------------                     
Department EmployeeName EmployeeID     Department EmployeeName EmployeeID

------------------------------------      ------------------------------
Accounts     Raj           123           Accounts     Raj           123
Accounts     Alok          124                        Alok          124
Accounts     Amit          125                        Amit          125
Sales        Henry         263           Sales        Henry         263 
Sales        Mark          454                        Mark          454
Hr           Lusi          342           Hr           Lusi          342  
Hr           Rosy          637                        Rosy          637

Upvotes: 1

Views: 81

Answers (4)

Atul Rai
Atul Rai

Reputation: 350

SELECT Department, EmployeeName, EmployeeID FROM table_name GROUP BY Department;

Upvotes: 0

Somil
Somil

Reputation: 597

A Simpler way , : formatting can be done at application level.

select Department, GROUP_CONCAT(EmployeeName),GROUP_CONCAT(EmployeeID) from t1 group by Department;

Upvotes: 0

Dylan Su
Dylan Su

Reputation: 6065

Here is a solution with a full demo.

SQL:

-- data
create table t1(Department char(20), EmployeeName char(20), EmployeeID int);
insert into t1 values      
('Accounts',     'Raj',           123  ),         
('Accounts',     'Alok',          124  ),         
('Accounts',     'Amit',          125  ),       
('Sales',        'Henry',         263  ),       
('Sales',        'Mark',          454  ),       
('Hr',           'Lusi',          342  ),       
('Hr',           'Rosy',          637  );
select * from t1;

-- Query needed
SELECT 
    CASE WHEN @last_dep IS NULL OR @last_dep != Department
            THEN (@last_dep:=Department)
        WHEN @last_dep = Department
            THEN ''
        END AS Department,
    EmployeeName,
    EmployeeID
FROM t1 JOIN (SELECT @last_dep:=NULL) v;

Output:

mysql> select * from t1;
+------------+--------------+------------+
| Department | EmployeeName | EmployeeID |
+------------+--------------+------------+
| Accounts   | Raj          |        123 |
| Accounts   | Alok         |        124 |
| Accounts   | Amit         |        125 |
| Sales      | Henry        |        263 |
| Sales      | Mark         |        454 |
| Hr         | Lusi         |        342 |
| Hr         | Rosy         |        637 |
+------------+--------------+------------+
7 rows in set (0.00 sec)

mysql>
mysql> -- Query needed
mysql> SELECT
    ->     CASE WHEN @last_dep IS NULL OR @last_dep != Department
    ->             THEN (@last_dep:=Department)
    ->         WHEN @last_dep = Department
    ->             THEN ''
    ->         END AS Department,
    ->     EmployeeName,
    ->     EmployeeID
    -> FROM t1 JOIN (SELECT @last_dep:=NULL) v;
+------------+--------------+------------+
| Department | EmployeeName | EmployeeID |
+------------+--------------+------------+
| Accounts   | Raj          |        123 |
|            | Alok         |        124 |
|            | Amit         |        125 |
| Sales      | Henry        |        263 |
|            | Mark         |        454 |
| Hr         | Lusi         |        342 |
|            | Rosy         |        637 |
+------------+--------------+------------+
7 rows in set (0.00 sec)

Upvotes: 0

Shailesh Singh
Shailesh Singh

Reputation: 421

try the distinct query for selecting unique values

check this link SQL/mysql - Select distinct/UNIQUE but return all columns?

Upvotes: 1

Related Questions