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

Question

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

Answer 1

yes, you can just include the column you would like to order..

DECLARE @Table table( sno int, sname varchar(200))

INSERT INTO @Table(sno,sname)
VALUES (1,'1Test'),(2,'2Test'),(3,'3Test')

SELECT * FROM @table WHERE sno = 2
UNION
SELECT * FROM @table WHERE sno = 1
UNION
SELECT * FROM @table WHERE sno = 3
ORDER BY sno desc

---

Hope it Helps!!

Answer 2

Post sql statements:

CREATE TABLE ...

INSERT INTO ...

and the output you expect.

---

A Fan of SSIS, SSRS and SSAS

Answer 3

Hi,

This is one way (without using temp tables):

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
order by 1,2