Get a list of only the orders used in the latest report in SQL Server

Question

In SQL Server I have been given a function

get_orders('account_number', 'date_from', 'date_to')

The function returns a bunch of columns, of interest are:

• orderId
• lastUsedInReportId
• lastUsedDate

I cannot modify the function.

I need to create a query that gets the complete list of orders for a given account_number (and from/to dates) but it should only show the latest used orders. Latest defined by the lastUsedDate.

I managed to come up with this query:

SELECT
    *
FROM
    (SELECT
         orderId, MAX(lastUsedInReportId) AS used_in
     FROM
         get_orders('account_number', 'date_from', 'date_to')
     GROUP BY
         orderId) AS latest_orders
INNER JOIN
    get_orders('account_number', 'date_from', 'date_to') orders ON orders.orderId = latest_orders.orderId 
                                                                AND orders.lastUsedInReportId = latest_orders.used_in;

Which seems to work fine as long as the order has been used in a report already.

But if the orders have not been used, the query returns nothing.

how can I change the query so it works well for all orders even if they have not been used in a report?

So if I have:

orderId	lastUsedInReportId	lastUsedDate
123	1	2021-01-01
123	2	2021-05-02

The query should return only the second row.

And if I have

orderId	lastUsedInReportId	lastUsedDate
123	1	2021-01-01
123	2	2021-05-02
124	1	2021-01-01
124	2	2021-05-02
126

it should return rows 2,4 and 5

and

orderId	lastUsedInReportId	lastUsedDate
123
124
125
126
127

it should return all rows.

Thanks