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
hi
please try
UNION ALL
instead of
UNION
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