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

Question

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

Hey Somil Check this post.and plz help me to solve this – Akash Sourav Nayak Mar 11 '16 at 07:08 — Akash Sourav Nayak, Mar 11 '16 at 07:08

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

try the distinct query for selecting unique values

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

edited May 23 '17 at 11:50

Community

1
1

answered Mar 11 '16 at 06:59

Shailesh Singh

421
2
8

score 0 · Accepted Answer · answered Mar 11 '16 at 07:14

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)

what do you want to avoid by using subquery? the user variable? — Dylan Su, Mar 11 '16 at 09:22
yes we want to avoid user variable.. and we have to do it by using SubQuerry only. — Akash Sourav Nayak, Mar 11 '16 at 11:08

score 0 · Answer 3 · answered Mar 11 '16 at 07:29

0

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

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

answered Mar 11 '16 at 07:29

Somil

567
5
13

score 0 · Answer 4 · answered Mar 11 '16 at 07:41

0

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

answered Mar 11 '16 at 07:41

Atul Rai

242
2
10

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

4 Answers4