Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query who return something like it :

Name   Gender Job         date of hire
John   M      mechanic    2012-05-08
John   M      electrician 2010-01-01
Vicky  F      scientific  2012-11-11
Bob    M      NULL        NULL

I need to have the name, gender and jobname of first job of each person. But i don't know how. My query look like this :

select name,gender,jobname,hiredate
from person p
left join job j on p.personid = j.personid

I'm on Microsoft SQL Server 2000

I need this as result :

Name   Gender Job         
John   M      electrician
Vicky  F      scientific 
Bob    M      NULL       
share|improve this question
1  
Listing 5 here works on SQL Server 2000 but (as the article says) is somewhat complex and unintuitive. –  Martin Smith Dec 4 '12 at 16:12
    
Thank you, it is working. My query look like this now : select name,gender,SUBSTRING(job, 9, 50) as 'jobname' from( select name,gender,min(CONVERT(CHAR(8), hiredate, 112)+jobname) as'job' from person p left join job j on p.personid = j.personid group by name ) as query –  Jonlabr Dec 4 '12 at 17:07
    
When I try that query I get an error message "Column 'person.Gender' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." –  Paul White Dec 5 '12 at 14:40
add comment

2 Answers

up vote 6 down vote accepted

I infer that your data looks like this:

Person Table

╔══════════╦═══════╦════════╗
║ PersonID ║ Name  ║ Gender ║
╠══════════╬═══════╬════════╣
║        1 ║ John  ║ M      ║
║        2 ║ Vicky ║ F      ║
║        3 ║ Bob   ║ M      ║
╚══════════╩═══════╩════════╝

Job Table

╔══════════╦═════════════╦════════════╗
║ PersonID ║   JobName   ║  HireDate  ║
╠══════════╬═════════════╬════════════╣
║        1 ║ Electrician ║ 2010-01-01 ║
║        1 ║ Mechanic    ║ 2012-05-08 ║
║        2 ║ Scientific  ║ 2012-11-11 ║
╚══════════╩═════════════╩════════════╝

The first task is to find the first job (by hire date) for each person. One neat way to do that is by using a correlated subquery:

SELECT j.*
FROM dbo.Job AS j
WHERE 
    j.HireDate =
    (
        SELECT MIN(j2.HireDate)
        FROM dbo.Job AS j2
        WHERE j2.PersonID = j.PersonID
    );

Notice the correlation WHERE j2.PersonID = j.PersonID between the inner and outer queries there. The output of that query is:

╔══════════╦═════════════╦════════════╗
║ PersonID ║   JobName   ║  HireDate  ║
╠══════════╬═════════════╬════════════╣
║        1 ║ Electrician ║ 2010-01-01 ║
║        2 ║ Scientific  ║ 2012-11-11 ║
╚══════════╩═════════════╩════════════╝

The execution plan (given a clustered PRIMARY KEY on PersonID, HireDate) is:

First job plan

The interesting thing about that plan is the Job table is only scanned once, despite there being two references to it in the original query. The plan uses an optimization that I call Segment Top. Essentially the execution engine takes advantage of the index order to detect the start of a new group (segment) and take just the first row from each group (top).

Now that we have that result, all we need do is join it back to the Person table:

SELECT
    p.PersonName,
    p.Gender, 
    j.JobName
FROM dbo.Person AS p
LEFT JOIN
(
    -- Previous query
    SELECT j.*
    FROM dbo.Job AS j
    WHERE 
        j.HireDate =
        (
            SELECT MIN(j2.HireDate)
            FROM dbo.Job AS j2
            WHERE j2.PersonID = j.PersonID
        )
) AS j ON 
    j.PersonID = p.PersonID
OPTION (MERGE JOIN);

The execution plan is:

Final plan

The OPTION (MERGE JOIN) is not required; I just added it to show the plan you are likely to get when the tables contain a larger number of rows than in this small example.


Table definitions and sample data:

CREATE TABLE dbo.Person
(
    PersonID    integer NOT NULL,
    PersonName  varchar(30) NOT NULL,
    Gender      char(1) NOT NULL,

    PRIMARY KEY (PersonID)
);

CREATE TABLE dbo.Job
(
    PersonID    integer NOT NULL,
    JobName     varchar(30) NOT NULL,
    HireDate    datetime NOT NULL,

    PRIMARY KEY (PersonID, HireDate)
);

INSERT dbo.Person
    (PersonID, PersonName, Gender)
SELECT 1, 'John', 'M' UNION ALL
SELECT 2, 'Vicky', 'F' UNION ALL
SELECT 3, 'Bob', 'M';

INSERT dbo.Job
    (PersonID, JobName, HireDate)
SELECT 1, 'Mechanic', '20120508' UNION ALL
SELECT 1, 'Electrician', '20100101' UNION ALL
SELECT 2, 'Scientific', '20121111';
share|improve this answer
add comment

I did a mistake in the query in the comment. I forgot gender in the group by. This query work for me :

    select name,gender,SUBSTRING(job, 9, 50) as 'jobname'
    from(
        select name,gender,min(CONVERT(CHAR(8), hiredate, 112)+jobname) as'job' 
        from person p 
        left join job j on p.personid = j.personid 
        group by name,gender
     ) as query`
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.