CODEWITHSUNDEEP
mysqlsqlreturnuniquedistinct
sjw0525
sjw0525

Reputation: 329

Is there a way in MySQL to determine every time a record contains a value that is used for the first time?

I want to return HTML surrounding the department name every time it is a department name that is returned for the first time. My code right not is is below:

SELECT
Company.CompanyID,
Company.Bio,
Company.Email,
People.LastName,
People.FirstName,
Departments.DepartmentName AS DepartmentName,
Departments.DepartmentID
FROM
Company
LEFT JOIN People USING (CompanyID)
LEFT JOIN Departments
ON Departments.DepartmentID = Company.DepartmentID
ORDER BY
Departments.DepartmentID ASC,
People.LastName ASC,
People.FirstName ASC;

It returns the below:

 +---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
    |CompanyID| Bio            | Email      |LastName|FirstName|DepartmentName|DepartmentID| 
    +---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
    | BK001   | Lorem Ipsum ...| [email protected]| Smith | Jay      | Finance     | 1 | 
    | BK002   | Dolor sit am ..| [email protected]| Rhays | Jim      | Marketing   | 2 | 
    | BK003   | Consectetur .. | [email protected]| Carrs | Sam      | IT          | 3 | 
    | BK004   | Sed do eiusm...| [email protected]| Webb  | Bob      | Marketing   | 2 | 
    | BK005   | Concept Phi... | [email protected]| Hart  | Rob      | Design      | 4 |

I would like it to return the following:

|CompanyID| Bio            | Email      |LastName|FirstName|HTML|DepartmentName|DepartmentID| 
 +---------+-------------------------------------+-------------+---------+--------+--------+------------+----------
| BK001   | Lorem Ipsum ...| [email protected]| Smith | Jay      |<div>|Finance     | 1 | 
| BK002   | Dolor sit am ..| [email protected]| Rhays | Jim      |<div>|Marketing   | 2 | 
| BK003   | Consectetur .. | [email protected]| Carrs | Sam      |<div>|IT          | 3 | 
| BK004   | Sed do eiusm...| [email protected]| Webb  | Bob      |     |Marketing   | 2 | 
| BK005   | Concept Phi... | [email protected]| Hart  | Rob      |<div>|Design      | 4 |

Upvotes: 3

Views: 53

Answers (3)

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

Check This. 

    SET @row_number:=0;
    SET @DepartmentName:='';

    select 
    CompanyID,Bio,Email,LastName,FirstName,
    CASE When row_number ='1' then '<DIV>' else '' end as 'HTML',
    HTML
    DepartmentName,
    DepartmentID
    from
    (
    SELECT
    Company.CompanyID,
    Company.Bio,
    Company.Email,
    People.LastName,
    People.FirstName,
    Departments.DepartmentName AS DepartmentName,
    Departments.DepartmentID ,
    @row_number:=CASE WHEN @DepartmentName=DepartmentName THEN @row_number+1 ELSE 1 END AS row_number 
    FROM
    Company
    LEFT JOIN People USING (CompanyID)
    LEFT JOIN Departments
    ON Departments.DepartmentID = Company.DepartmentID
    ORDER BY
    Departments.DepartmentID ASC,
    People.LastName ASC,
    People.FirstName ASC;
    )a

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522346

One option is to add an additional subquery to select the first occurrence of a department. I assume that the CompanyID represents a chronological order in which a record were inserted into the table, and therefore this is a proxy for what appeared first.

This option might outperform using a subquery in the SELECT clause.

SELECT t1.CompanyID,
       t1.Bio,
       t1.Email,
       t2.LastName,
       t2.FirstName,
       CASE WHEN t4.DepartmentID IS NOT NULL THEN '<div>' ELSE '' END AS HTML,
       t3.DepartmentName AS DepartmentName,
       t3.DepartmentID
FROM Company t1
LEFT JOIN People t2
    ON t1.CompanyID = t2.CompanyID
LEFT JOIN Departments t3
    ON t3.DepartmentID = t1.DepartmentID
LEFT JOIN
(
    SELECT d.DepartmentID, MIN(c.CompanyID) AS firstCompanyID    -- this subquery finds
    FROM Company c                                               -- the first appearance of
    LEFT JOIN Departments d                                      -- a company for a given
        ON c.DepartmentID = d.DepartmentID                       -- department
    GROUP BY d.DepartmentID
) t4
    ON t3.DepartmentID = t4.DepartmentID AND
       t1.CompanyID    = t4.firstCompanyID
ORDER BY t3.DepartmentID,
         t2.LastName,
         t2.FirstName;

Notes: The reason we can get away with using MIN on the CompanyID column, which is text, is because the width of all the IDs is fixed as five. In this case, the lexicographic sorting will be in agreement with the numeric sorting of the digits, which is what we really care about. Note that this might not work if the width of this field were not fixed.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

Use Case statement and Sub-Query

SELECT CASE
         WHEN CompanyID = (SELECT Min(C.CompanyID)
                           FROM   Departments D1
                                  JOIN Company C
                                    ON d1.DepartmentID = c.DepartmentID
                           WHERE  D1.DepartmentName = Departments.DepartmentName) THEN '<div>'
         ELSE ''
       END AS HTML,
    ...
FROM ..

Note : Use Alias name to qualify columns instead of writing full table name

Upvotes: 1

Related Questions