How to optimize my query

1

I wonder if anyone can teach me how to optimize this example. I would be grateful!

Here's my code, keying off the Cusno. This is designed to show poor maintenance when the
ListOfCustomers table is not cleaned up, and I have to get data anyway.

CREATE TABLE OrderHeader(OrderNumber int, Product varchar(10), Cusno int, City varchar(100))
INSERT INTO OrderHeader VALUES(10001, 'Bicycle', 987, 'San Francisco')

CREATE TABLE ListOfCustomers(Cusno int, FirstName varchar(100), LastName varchar(100), City varchar(100))
INSERT INTO ListOfCustomers VALUES(987, 'Fred', 'Walker', 'San Francisco')
INSERT INTO ListOfCustomers VALUES(987, 'Fred', 'Walker', 'San Mateo')
INSERT INTO ListOfCustomers VALUES(987, 'Fred', 'Walker', 'Santa Cruz')

My first attempt at a select went like this:

SELECT
a.OrderNumber,
b.City
FROM OrderHeader a
LEFT OUTER JOIN ListOfCustomers b ON b.Cusno = a.Cusno

But since the ListofCustomers table has 3 records for the same person when I only needed one,
I solved it this way (I only needed the first occurrence, without knowing the city):

select
a.OrderNumber,
City=(SELECT TOP 1 City FROM ListOfCustomers where Cusno = a.Cusno)
FROM OrderHeader a

Is there a better way to do this?

I wish where was a better way to get just one record from the ListOfCustomers when I just need
the guy's name, and I don't need to know what city.

2

learned this from @harishgg1

select top 1 with ties
    *
from ListOfCustomers l
join OrderHeader o on l.Cusno = o.Cusno
order by row_number() over (partition by l.Cusno order by l.City desc)
1 Like
3 
SELECT
a.OrderNumber,
b.City
FROM OrderHeader a
OUTER APPLY (
    SELECT TOP (1) *
    FROM dbo.ListOfCustomers b
    WHERE b.Cusno = a.Cusno
) AS b
ORDER BY OrderNumber