How to use Order Before before Union on sql Server using this query?

Navigation
1

I have this query: 

    SELECT * FROM (

SELECT distinct
        db_name() as 'Banco_de_Dados',g.KM_SIMBOL, fotos = COUNT(*) OVER (PARTITION BY d.globalid ORDER BY d.globalid),d.GDB_FROM_DATE as Data , g.RODOVIA , g.CODIGO 
    FROM [dbo].GO_35_BR070_PE_VERTICAL_MANUTENCAO d
        INNER JOIN [dbo].GO_35_BR070_PE_VERTICAL_MANUTENCAO__ATTACH a
    ON a.REL_GLOBALID = d.GlobalID
        INNER JOIN GO_35_BR070_PE_VERTICAL  g
    ON D.vertical_rel_globalid= G.VERTICAL_GLOBALID


                 ) d 


    WHERE fotos > 2
    order by d.Data

        UNION  --USEI UNION PARA JUNTAR O RESULTADO DE UM MESMO BANCO DE DADOS

    SELECT * FROM (
SELECT distinct
         db_name() as 'Banco_de_Dados',g.KM_SIMBOL, fotos = COUNT(*) OVER (PARTITION BY d.globalid ORDER BY d.globalid),d.GDB_FROM_DATE as Data , g.RODOVIA , g.CODIGO 
    FROM [dbo].GO_35_BR070_TREVOGIRASSOL_PE_VERTICAL_MANUTENCAO d
        INNER JOIN [dbo].GO_35_BR070_TREVOGIRASSOL_PE_VERTICAL_MANUTENCAO__ATTACH a
    ON a.REL_GLOBALID = d.GlobalID 
        INNER JOIN GO_35_BR070_PE_VERTICAL  g 
    ON D.vertical_rel_globalid= G.VERTICAL_GLOBALID


                  ) d

    WHERE fotos > 2
    order by d.Data

WHEN I USE THAT WAY THE "UNION" IS SUBLIMATED IN RED SIGNING THAT THERE IS ERROR. But if I take UNION to my query it works without problem. Anyone to help me?

    
asked by anonymous 12.09.2017 / 20:44

2 answers

2

So, see if you have everything in another select and including a sort column resolves: 

Select * from 
(
        SELECT * FROM (
                        SELECT distinct
                                1 as ordem,            
                                db_name() as 'Banco_de_Dados',g.KM_SIMBOL, fotos = COUNT(*) OVER (PARTITION BY d.globalid),d.GDB_FROM_DATE as Data , g.RODOVIA , g.CODIGO 
                        FROM [dbo].GO_35_BR070_PE_VERTICAL_MANUTENCAO d
                        INNER JOIN [dbo].GO_35_BR070_PE_VERTICAL_MANUTENCAO__ATTACH a
                        ON a.REL_GLOBALID = d.GlobalID
                        join GO_35_BR070_PE_VERTICAL  g ON D.vertical_rel_globalid= G.VERTICAL_GLOBALID
                        ) d 
        WHERE fotos > 2

        UNION  --USEI UNION PARA JUNTAR O RESULTADO DE UM MESMO BANCO DE DADOS

        SELECT * FROM (
                        SELECT distinct
                        2 as ordem,
                        db_name() as 'Banco_de_Dados',g.KM_SIMBOL, fotos = COUNT(*) OVER (PARTITION BY d.globalid ),d.GDB_FROM_DATE as Data , g.RODOVIA , g.CODIGO 
                        FROM [dbo].GO_35_BR070_TREVOGIRASSOL_PE_VERTICAL_MANUTENCAO d
                            INNER JOIN [dbo].GO_35_BR070_TREVOGIRASSOL_PE_VERTICAL_MANUTENCAO__ATTACH a
                        ON a.REL_GLOBALID = d.GlobalID 
                        join GO_35_BR070_PE_VERTICAL  g ON D.vertical_rel_globalid= G.VERTICAL_GLOBALID
                        ) d    
        WHERE fotos > 2
        ) a 
Order by Ordem, Data
    
12.09.2017 / 20:57
0

To correct I simply added the a in front of the Order By that is in that code that our friend @Alexandre Cavaloti sent to me here. o

  

"a"

is the reference to the table with "ATTACH" on the first select as for the second select. 

 Select * from 
    (
            SELECT * FROM (
                            SELECT distinct
                                    1 as ordem,            
                                    db_name() as 'Banco_de_Dados',g.KM_SIMBOL, fotos = COUNT(*) OVER (PARTITION BY d.globalid),d.GDB_FROM_DATE as Data , g.RODOVIA , g.CODIGO 
                            FROM [dbo].GO_35_BR070_PE_VERTICAL_MANUTENCAO d
                            INNER JOIN [dbo].GO_35_BR070_PE_VERTICAL_MANUTENCAO__ATTACH a
                            ON a.REL_GLOBALID = d.GlobalID
                            join GO_35_BR070_PE_VERTICAL  g ON D.vertical_rel_globalid= G.VERTICAL_GLOBALID
                            ) d 
            WHERE fotos > 2

            UNION  --USEI UNION PARA JUNTAR O RESULTADO DE UM MESMO BANCO DE DADOS

            SELECT * FROM (
                            SELECT distinct
                            2 as ordem,
                            db_name() as 'Banco_de_Dados',g.KM_SIMBOL, fotos = COUNT(*) OVER (PARTITION BY d.globalid ),d.GDB_FROM_DATE as Data , g.RODOVIA , g.CODIGO 
                            FROM [dbo].GO_35_BR070_TREVOGIRASSOL_PE_VERTICAL_MANUTENCAO d
                                INNER JOIN [dbo].GO_35_BR070_TREVOGIRASSOL_PE_VERTICAL_MANUTENCAO__ATTACH a
                            ON a.REL_GLOBALID = d.GlobalID 
                            join GO_35_BR070_PE_VERTICAL  g ON D.vertical_rel_globalid= G.VERTICAL_GLOBALID
                            ) d    
            WHERE fotos > 2
            )a



    Order by Ordem, Data
    
13.09.2017 / 16:16
How can I access already modified table data? Do you need to use the AppCompat and Support Design libraries when the minimum API is 21?