How to use Order By before Union in sql Server using this query?

Asked

Viewed 600 times

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 it this way the "Union" is sublimated in red signaling error. But if you take out UNION my query works fine.Someone to help me out?

sql-server

  • because you need to order before the Union?

  • Take off the d. date of order by. Use: order by data

  • @rLines to filter the first query and then join the second query using Union

  • @Wesleyferreira even if I take it will not change anything dear friend :(

2 answers

2


Then see if encompassing everything in another select and including a resolve sorting column:

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

  • my problem is not that exactly, because using Over with Count works yes. if I take this Union it works without problem. But by making a mistake.

  • I changed the answer, put a sorting column in the 2 selects and wrapped with another select by putting the order by only in it.

  • Thanks @Alexandre but I fixed what you did. It’s not what’s wrong. what you did is right only it lacked a small detail to run.I will post the correction and explaining what I did - Gladiador 1 hour ago

0

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

"to"

is the reference for the table with "ATTACH" in 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

  • It is true, I made this correction in my select local but had not put in the example code. I will adjust in the above solution also.

  • @Alexandrecavaloti was worth my friend.you helped a lot!

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.