Order by or filter in Sql Server

Question

It’s possible to use order by or some filter in Ner Join or left Join or I’m trying to do it wrong. I need to bring in a query a column that is in a daughter table, but I need it to be the column of the most recent record of the daughter table records. To solve this problem I made a sub-query and solved the problem well, but I wanted to know if I could make a Join with top 1 and ordered by a column.

Answer 1

Good morning!

I believe I could do so too, ie put a TOP 1 in a select as if it were a column, with the Where = the outside column. So it will bring the last record of each item consulted.

Select 

CT1.coluna_1_tabela_1,
(select TOP 1 CT3.coluna_1_tabela_3 from Tabela3 CT3 where CT3.coluna_1_tabela_3 = CT1.coluna_1_tabela_1 order BY CT3.coluna_1_tabela_3 DESC) as Seu_resultado

from Tabela1 CT1
   INNER JOIN Tabela2 CT2
      on CT1.coluna_1_tabela_1 = CT2.coluna_1_tabela_2
where CT1.coluna_10_tabela1 = 'XYZ'
order by CT1.coluna_1_tabela_1

Answer 2

What you want can’t be solved with Cross Apply? Here’s an example of how to use, you’re just not ordering, but then you edit for what applies to your case.

SELECT  Employee.EmployeeId ,
        FirstName ,
        LastName ,
        PhoneType ,
        PhoneNumber
FROM    dbo.Employee
        CROSS  APPLY ( SELECT TOP 1
                                PhoneNumber ,
                                PhoneType ,
                                EmployeeId
                      FROM      dbo.PhoneNumber
                      WHERE     PhoneType = 4
                                AND EmployeeId = Employee.EmployeeId ) p
ORDER BY EmployeeId;

Answer 3

Below is a script on how to do what you would like, but if you can post what you did to better understand it will be clearer for everyone.

Select TOP 10 TB1.COLUNANOVA 
from Tabela1 TB1
   inner join Tabela2 TB2
      on TB1.CODPRIMARIO = TB2.CODIGOPRIMARIO
where TB1.COLUNAANTIGA = "resultado"
order by TB1.COLUNANOVA