Try:

select
x.*, t.Company
from
(
SELECT
EmpNo
, MIN(WEDateFr) WEDateFr
, MAX(WEDateTo) WEDateTo
FROM Table1
GROUP BY EmpNo
) as x
join Table1 t on t.EmpNo = x.EmpNo
and t.WEDateFr = x.WEDateFr


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
My previous post contained an error. The following information is correct.

[quoted text, click to view]

Thanks Tom, I'd made a mistake

[quoted text, click to view]

Hi there

I have a table with three fields, viz. EmployeeNo, WorkDate, Company.
Records for a particular employee (viz. 660) are given as follows:

EmpNo WEDate Company
660 19990530 SA Cargo
660 19990606 SA Cargo
660 19990613 SA Cargo
660 20000620 SA Beverages
660 20010620 SA Beverages
660 20020620 SA Beverages
660 20030620 SA Beverages

I would like to select, for each employee, the company for which they worked
on the latest date that they were employed, ie. for employee 660, the latest
date would be 2003/06/20 and the company would thus be SA Beverages.

I am currently using a GROUP BY clause as follows:

SELECT EmpNo, MAX(WEDate)
FROM Table1
GROUP BY EmpNo

The above statement only gives me the employee number and latest work date.
How could I get the corresponding company too?

Best regards
Loane

My previous post contained an error. The following information is correct.

[quoted text, click to view]

The code is correct. Using your data as shown below, I get only one row
returned:

create table Table1
(
EmpNo int not null
, WEDateFr datetime not null
, WEDateTo datetime not null
, Company varchar (20) not null
, primary key (EmpNo, WEDateFr)
)
go


insert Table1 values (660, '19990530', '19990601', 'SA
Cargo')
insert Table1 values (660, '19990606', '19990610', 'SA
Cargo')
insert Table1 values (660, '19990613', '19990701', 'SA
Cargo')
insert Table1 values (660, '20000620', '20000930', 'SA
Beverages')
insert Table1 values (660, '20010620', '20010715', 'SA
Beverages')
insert Table1 values (660, '20020620', '20020831', 'SA
Beverages')
insert Table1 values (660, '20030620', '20031231', 'SA
Beverages')
go

select
x.*, t.Company
from
(
SELECT
EmpNo
, MIN(WEDateFr) WEDateFr
, MAX(WEDateTo) WEDateTo
FROM Table1
GROUP BY EmpNo
) as x
join Table1 t on t.EmpNo = x.EmpNo
and t.WEDateFr = x.WEDateFr
go

drop table Table1

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
Hi Tom
This works great, except with the JOIN I get multiple rows with the same
results. I would like to avoid using a SELECT DISTINCT query for performance
reasons. Is this possible?
Best regards
Loane

[quoted text, click to view]

Hi Tom
This works great, except with the JOIN I get multiple rows with the same
results. I would like to avoid using a SELECT DISTINCT query for performance
reasons. Is this possible?
Best regards
Loane

[quoted text, click to view]

Again, please post your DDL, so that we can test. I'm thinking that a GROUP
BY may have been left out. Perhaps a JOIN as well.



--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
Hi there

When I run this piece of T-SQL (as part of a bigger SELECT query), I get the
following error message:

Server: Msg 116, Level 16, State 1, Line 2
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.

My query is as follows:

USE candidate

SELECT c.admref 'AdmRef',

(SELECT MIN (CASE WHEN x.Position = 1 THEN x.languages END) 'Lang1',
MIN (CASE WHEN x.Position = 2 THEN x.languages END) 'Lang2',
MIN (CASE WHEN x.Position = 3 THEN x.languages END) 'Lang3',
MIN (CASE WHEN x.Position = 4 THEN x.languages END) 'Lang4'
FROM
( SELECT n.admref, n.languages,
(
SELECT (COUNT (*) + 1) FROM f01languages
WHERE c.admref = n.admref
AND n.languages > languages
) AS Position
FROM f01languages n
) x
WHERE
x.Position <= 4)

FROM f01candidate c

WHERE c.admref < 500

Please help
Best regards
Loane

Hi there

When I run this piece of T-SQL (as part of a bigger SELECT query), I get the
following error message:

Server: Msg 116, Level 16, State 1, Line 2
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.

My query is as follows:

USE candidate

SELECT c.admref 'AdmRef',

(SELECT MIN (CASE WHEN x.Position = 1 THEN x.languages END) 'Lang1',
MIN (CASE WHEN x.Position = 2 THEN x.languages END) 'Lang2',
MIN (CASE WHEN x.Position = 3 THEN x.languages END) 'Lang3',
MIN (CASE WHEN x.Position = 4 THEN x.languages END) 'Lang4'
FROM
( SELECT n.admref, n.languages,
(
SELECT (COUNT (*) + 1) FROM f01languages
WHERE c.admref = n.admref
AND n.languages > languages
) AS Position
FROM f01languages n
) x
WHERE
x.Position <= 4)

FROM f01candidate c

WHERE c.admref < 500

Please help
Best regards
Loane