Asad Mahmood Asad Mahmood - 2 months ago 12
SQL Question

How to make a DISTINCT CONCAT statement?

New SQL developer here, how do I make a

DISTINCT
CONCAT
statement?

Here is my statement without the
DISTINCT
key:

COLUMN Employee FORMAT a25;
SELECT CONCAT(CONCAT(EMPLOYEEFNAME, ' '), EMPLOYEELNAME) AS "Employee", JOBTITLE "Job Title"
FROM Employee
ORDER BY EMPLOYEEFNAME;


Here is it's output:

Employee Job Title
------------------------- -------------------------
Bill Murray Cable Installer
Bill Murray Cable Installer
Bob Smith Project Manager
Bob Smith Project Manager
Frank Herbert Network Specilist
Henry Jones Technical Support
Homer Simpson Programmer
Jane Doe Programmer
Jane Doe Programmer
Jane Doe Programmer
Jane Fonda Project Manager
John Jameson Cable Installer
John Jameson Cable Installer
John Carpenter Technical Support
John Carpenter Technical Support
John Jameson Cable Installer
John Carpenter Technical Support
John Carpenter Technical Support
Kathy Smith Network Specilist
Mary Jane Project Manager
Mary Jane Project Manager

21 rows selected


If I were to use the
DISTINCT
key I should only have 11 rows selected, however
if I use
SELECT DISTINCT CONCAT
I get an error.

Answer

One option is to use GROUP BY:

SELECT CONCAT(CONCAT(EMPLOYEEFNAME, ' '), EMPLOYEELNAME) AS "Employee",
       JOBTITLE AS "Job Title" 
FROM Employee
GROUP BY CONCAT(CONCAT(EMPLOYEEFNAME, ' '), EMPLOYEELNAME),
         JOBTITLE
ORDER BY "Employee"

Another option, if you really want to use DISTINCT, would be to subquery your current query:

SELECT DISTINCT t.Employee,
                t."Job Title"
FROM
(
    SELECT CONCAT(CONCAT(EMPLOYEEFNAME, ' '), EMPLOYEELNAME) AS "Employee",
           JOBTITLE AS "Job Title" 
    FROM Employee
) t