CODEWITHSUNDEEP
sqlouter-apply
Qëndrim Izairi
Qëndrim Izairi

Reputation: 27

Im trying to use DISTINCT and ORDER BY DESC with OUTER APPLY (2 tables), cannot get DESC order

I am using the following query and i manage to get results ordered by Id ASC (from Table2), however I want to get the result ordered by Id (table2) DESC.

Table 1:

ID CustomerNumber Name Surname
1023 000001 Name1 Surname1
1024 000002 Name2 Surname2

Table 2:

Id CustomerNumber InvoiceNr InvoiceMonth
14435 001394 98412018 9-2018
14436 002061 98422018 9-2018 
SELECT c.ID, c.CustomerNumber, c.Name, c.Surname, c.Area, c.City, c.Address, c.PhoneNumber, c.CustomerTypeID, c.Enabled, c.DateCreated, p.Id, p.Debit, p.Credit
                FROM TblCustomer c OUTER APPLY
                     (SELECT DISTINCT TOP 5 p.*
                      FROM TblPayments p
                      WHERE c.CustomerNumber = p.CustomerNumber
                     )p WHERE c.ID =  1023

the results in SQL

Upvotes: -2

Views: 882

Answers (1)

Qëndrim Izairi
Qëndrim Izairi

Reputation: 27

The following query solved my problem:

SELECT c.ID as pid, c.CustomerNumber, c.Name, c.Surname, c.Area, c.City, c.Address, c.PhoneNumber, c.CustomerTypeID, c.Enabled, c.DateCreated, p.Id, p.Debit, p.Credit
                FROM TblCustomer c OUTER APPLY
                     (SELECT DISTINCT TOP 5 p.*
                      FROM TblPayments p
                      WHERE c.CustomerNumber = p.CustomerNumber
                      ORDER BY p.id DESC 
                     ) p WHERE c.ID = 1023

I hadn't included the following line to the query:

ORDER BY p.id DESC

Upvotes: 1

Related Questions