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

Question

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

You've already been asked for sample data (not [images](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question) of data) over 8 hours ago. If you post a question you [really should revisit every so often](https://idownvotedbecau.se/beingunresponsive) to see if someone has commented and asked for clarification — Stu, Mar 05 '22 at 09:52
I edit my question and provided samples @D-Shih Stu, you are right, however, for now I am situated in some African country which is a bit different from a normal world. — Qëndrim Izairi, Mar 05 '22 at 19:20

score 1 · Answer 1 · edited Aug 11 '23 at 13:55

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

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

1 Answers1