How to use TOP 1 in a T-SQL sub-query?
Posted on 2016-10-25
Can someone help me with the correct syntax for a T-SQL query I am trying to apply in a SQL Server 2005 database?
I have TABLE_A that contains unique Order Items and a View VW_B that might contain multipe instances of items from TABLE_A along with further columns concerning the Invoices these Order Items might have appeared on.
I would like to create a query that selects each item from TABLE_A and details of only a single instance of them from VW_B (the latest Invoiced they featured on).
I have been trying suggestions from other web-sites but either get syntax errors or only a single row of output.
My latest attempt has been along the lines of :
SELECT A.Col1, A.Col2, B.*
FROM TABLE_A AS A
(SELECT TOP 1 VB.INVREF, VB.INVDATE
FROM VW_B AS VB
ORDER BY VB.INVDATE DESC
WHERE VB.ITEMID = A.ITEMID) AS B
The above format produces a syntax error with the keyword "WHERE" on Line 7.
Is it because I am using a View rather than a Table in my "SELECT TOP 1 ... " sub-query?
I hope someone can understand what I am trying to do and show me where I am going wrong.
Thanks & regards.