Need to have ordered results in one shot query instead of 3 queries

Transact-SQL
1

Hello,

I have the following query where i do 3 searches and then make union between them so that the results are ordered, is there a way i can do this in one shot without losing the ordering?

select 0 as cc,idautosub from XXX.qrybooksvendors where Author1='sam david' or Author2='sam david' or Author3='sam david' and availability<>5 union select 1 as cc,idautosub from XXX.qrybooksvendors where title='sam david' and availability<>5 union select 2 as cc,idautosub from XXX.qrybooksvendors where ( contains(search,'"sam" AND "david"') ) and availability<>5

thank you

2

hi

please try

UNION ALL
instead of
UNION

3

You can add
ORDER BY 1, 2
at the end to order the whole resulting dataset

As harishgg1 mentioned, use
UNION ALL
instead of
UNION

A UNION will create a DISTINCT dataset: behind the scene the dataset will first be sorted, then have all its doubles removed. That takes time.
In many cases UNION ALL should be the standard way to go. Only use UNION in case doubles can occur.

If

SELECT 1 AS cc
	,idautosub
FROM XXX.qrybooksvendors
WHERE title = 'sam david'
	AND availability <> 5

could result in non-unique records, you can use DISTINCT

SELECT DISTINCT 1 AS cc
	,idautosub
FROM XXX.qrybooksvendors
WHERE title = 'sam david'
	AND availability <> 5

Mixing AND and OR.
I learned to use parenthesis every time I have to mix OR and AND. It's a great source of hard to find bugs.

AND has precedence over OR. The SQL script

WHERE Author1 = 'sam david'
	OR Author2 = 'sam david'
	OR Author3 = 'sam david'
	AND availability <> 5

will be interpreted as

WHERE Author1 = 'sam david'
	OR Author2 = 'sam david'
	OR (Author3 = 'sam david'
	AND availability <> 5)
SQL script 
SELECT 0 AS cc
	,idautosub
FROM XXX.qrybooksvendors
WHERE (Author1 = 'sam david'
	OR Author2 = 'sam david'
	OR Author3 = 'sam david')
	AND availability <> 5

UNION ALL

SELECT 1 AS cc
	,idautosub
FROM XXX.qrybooksvendors
WHERE title = 'sam david'
	AND availability <> 5

UNION ALL

SELECT 2 AS cc
	,idautosub
FROM XXX.qrybooksvendors
WHERE (
		CONTAINS (
			search
			,'"sam" AND "david"'
			)
		)
	AND availability <> 5
ORDER BY 1, 2