limit and order in union statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

limit and order in union statement

Author

Topic

BlackMustard
Starting Member

4 Posts

Posted - 2008-03-18 : 17:59:00

hi all,

i have the following union statement, which works like a charm:

SELECT [Name], [EventID] AS [ItemID], [TourID], [Date], NULL AS [StartDate], [Date] AS [SortDate], [Type] FROM [Events] WHERE [TourID] IS NULL AND AccessLevel <= @AuthenticationLevel UNION SELECT [Name], [TourID] AS [ItemID], [TourID], NULL AS [Date], [StartDate], [StartDate] AS [SortDate], '2' AS [Type] FROM [Tours]WHERE AccessLevel <= @AuthenticationLevel ORDER BY [SortDate]

now i want to ad a limit to this statement, but i can't get it working. i have tried this:

SELECT * FROM (SELECT [Name], [EventID] AS [ItemID], [TourID], [Date], NULL AS [StartDate], [Date] AS [SortDate], [Type] FROM [Events] WHERE [TourID] IS NULL AND AccessLevel <= @AuthenticationLevel UNION SELECT [Name], [TourID] AS [ItemID], [TourID], NULL AS [Date], [StartDate], [StartDate] AS [SortDate], '2' AS [Type] FROM [Tours]WHERE AccessLevel <= @AuthenticationLevel) ORDER BY [SortDate] LIMIT 2

but i keep getting an error message saying "Incorrect syntax near the keyword "ORDER". what am i doing wrong?

-bm

BlackMustard
Starting Member

4 Posts

Posted - 2008-03-18 : 18:02:31

oh, and by the way: i am using sql server 2005 (express locally and standard on my hosting service, but both fail) with asp.net 2.0.

-bm

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-18 : 19:51:52

When you say Limit, you mean to just 2 rows (or n-number of rows)?

SELECT TOP 2
	*
FROM
	(
		SELECT
			[Name],
			[EventID] AS [ItemID],
			[TourID],
			[Date],
			NULL AS [StartDate],
			[Date] AS [SortDate],
			[Type]
		FROM
			[Events]
		WHERE
			[TourID] IS NULL
			AND AccessLevel <= @AuthenticationLevel
		
		UNION
		
		SELECT
			[Name],
			[TourID] AS [ItemID],
			[TourID],
			NULL AS [Date],
			[StartDate],
			[StartDate] AS [SortDate],
			'2' AS [Type]
		FROM
			[Tours]
		WHERE
			AccessLevel <= @AuthenticationLevel
	)
ORDER BY
	[SortDate]

BlackMustard
Starting Member

4 Posts

Posted - 2008-03-19 : 02:43:23

i was under the impression that the TOP syntax was used only in access, and that the MSSQL equivalent was LIMIT - hence my wording.

anyway, i tried your statement but still got the same error: "Incorrect Syntax near keyword 'ORDER'"

...?

-bm

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2008-03-19 : 04:57:31

A derived table must have a correlation name.