Union HELP!!!!!

[cdietschrun]

CREATE PROCEDURE dbo.sp_GetNewestTemplates

AS

SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Joe Smith'
order by lastchange desc
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Jane Smith'
order by lastchange desc
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'GI Jane'
order by lastchange desc

GO

WHY does that code give no errors but this does:

CREATE PROCEDURE dbo.sp_GetNewestTemplates

AS

SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Joe Smith'
order by lastchange desc
union
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Jane Smith'
order by lastchange desc
union
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'GI Jane'
order by lastchange desc

GO

I need this to work! What am I missing, and how can I get around this ridiculously annoying error? All it says is "Error 156: Syntax error near union."

[Vimalsoft Pty Ltd]

what error are you getting ?

Vuyiswa Maseko, Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers." C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.somee.com http://www.vuyiswamaseko.tiyaneProperties.co.za vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[Lost User]

What happens when you remove the ORDER BY?

[cdietschrun]

The syntax works, but I have 3 disjoint selections as a result. I am looking to save one of the pieces of data in each row to a variable in VB.NET, if you can tell me how to do that without the union of the 3 results then that is fine. At the moment I don't know how

[David Skelly]

You are specifying ORDER BY too many times. You do not need to specify ORDER BY for each of the sub-queries; you only need to specify it once for the entire UNION result set.

SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Joe Smith'
union
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Jane Smith'
union
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'GI Jane'
order by lastchange desc

[Lost User]

The UNION concatenates the results, but you can use this result as if it were a query again, e.g.;

SELECT * FROM
(
SELECT TOP 1 [name] FROM sysobjects WHERE [name] LIKE ('sysh%')
UNION
SELECT TOP 2 [name] FROM sysobjects WHERE [name] LIKE ('sys%')
UNION
SELECT TOP 1 [name] FROM sysobjects WHERE [id] < 0
) AS myCTE ORDER BY [name]

[cdietschrun]

For Joe Smith, I have multiple rows that I want only the row that has both the name (WHERE TEMPLATEOWNER = 'Joe Smith') AND the row where the LASTCHANGE is the newest. So that first select should return the newest entry by Joe Smith. Then the second and third selects should find the one row that is the most recently updated by Jane and GI. The way it stands now, query analyzer shows three disjoint results. I need a joint record set. The removal of the order by in each select breaks the function.

[WoutL]

Try this. It should do exactly what you want

Select * from (
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Joe Smith'
order by lastchange desc
) d
union
Select * from (
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Jane Smith'
order by lastchange desc
) d
union
Select * from (
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'GI Jane'
order by lastchange desc
) d

Wout Louwers

[Tauseef A]

just remove column name in order by clause simly place column index .

Tauseef A Khan MCP Dotnet framework 2.0.