subquery synthax

[Daniel]

Good morning,

I have a table as shown below

[ID]
[Drawing ID]
[many other fields]

Where for a given [Drawing ID] there can exist numerous [ID]. I need to
create a query that will return only one entry per [Drawing ID], that of its'
greatest [ID] with all the other fields as well.

I have tried but to now success. Below is what I tried.

SELECT [Drawing Status Tbl].[Drawing ID], [Drawing Status Tbl].ID
FROM [Drawing Status Tbl]
WHERE ((([Drawing Status Tbl].ID)=(SELECT MAX(ID) FROM [Drawing Status Tbl]
WHERE [Drawing Status Tbl].[Drawing ID]=[Drawing Status Tbl].[Drawing ID])));

Thank you for the help,

Daniel

 

[Tom Ellison]

Dear Daniel:

In a correlated subquery, using distinct aliases for the tables is
indespensible. You would need to alias at least one of the two instances of
the table. Here, I alias both, which I strongly prefer.

SELECT [Drawing ID], ID
FROM [Drawing Status Tbl] T
WHERE [Drawing Status Tbl].ID = (SELECT MAX(ID)
FROM [Drawing Status Tbl] T1
WHERE T1.[Drawing ID] = T.[Drawing ID];

I'd bet this fixes it for you.

Tom Ellison