help with UNION

[caoamo]

I'm trying to build a single table query which displays the last date that a specific event (target change) was performed, with some details. The basic query I have looks like this:

SELECT TOP 1 chamberPosition AS [Position], targetID, date
FROM TargetChange_tbl
WHERE chamberPosition=1
GROUP BY chamberPosition, date, targetID, equipmentID
HAVING equipmentID=[Sputter tool?]
ORDER BY date DESC;

This works fine for only one chamberPosition. The problem is, each "Sputter tool" has 3 positions, and I want to build a query that displays the same info for all three chamber positions.

I've tried this code:

SELECT TOP 1 chamberPosition AS [Position], targetID, date
FROM TargetChange_tbl
WHERE chamberPosition=1
GROUP BY chamberPosition, date, targetID, equipmentID
HAVING equipmentID=[Sputter tool?]
ORDER BY date DESC;
UNION
SELECT TOP 1 chamberPosition AS [Position], targetID, date
FROM TargetChange_tbl
WHERE chamberPosition=2
GROUP BY chamberPosition, date, targetID, equipmentID
HAVING equipmentID=[Sputter tool?]
ORDER BY date DESC;
UNION
SELECT TOP 1 chamberPosition AS [Position], targetID, date
FROM TargetChange_tbl
WHERE chamberPosition=3
GROUP BY chamberPosition, date, targetID, equipmentID
HAVING equipmentID=[Sputter tool?]
ORDER BY date DESC;

The problem is, I am getting the FIRST occurance for chambers 2 and 3, instead of the last. When I change the ORDER BY clause to ascending, it keeps the same record for chambers 2 and 3, and simply applies the ORDER to the entire recordset.

Can anyone help me out?

 

[JarlH]

First of all, the TOP-construction is not ANSI SQL.

Secondly, according to the ANSI standard you can not specify ORDER BY for each SELECT in a UNION. You are only allowed to specify the ORDER BY to affect the complete query. (You see, ORDER BY is used to order the result-set of a query. Not to define the result-set.)

Then we come to the GROUP BY... That part makes no sense at all since you already do TOP 1. Remove! (Or rather, rewrite without TOP 1, since this is the ANSI SQL forum.)

I suggest you rewrite as something like:
SELECT DISTINCT chamberPosition AS [Position],
targetID,
date
FROM TargetChange_tbl as t1
WHERE chamberPosition=2
AND equipmentID=[Sputter tool?]
AND date = (SELECT MAX(date)
FROM TargetChange_tbl as t2
WHERE t1.targetID = t2.targetID
AND t1.equipmentID = t2.equipmentID
AND chamberPosition=2)
UNION ALL
SELECT ...