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

  • 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

    Wednesday, November 27, 2019 10:30 AM

All replies

  • 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!!

    Wednesday, November 27, 2019 3:16 PM
  • Post sql statements:

    CREATE TABLE ...

    INSERT INTO ...

    and the output you expect.


    A Fan of SSIS, SSRS and SSAS

    Wednesday, November 27, 2019 3:31 PM
  • 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

    Wednesday, November 27, 2019 6:14 PM