SQL select single record from one-to-many sub-query

I'm sure the SQL folk have a better names for these things... but anyway. If you have a one-to-many relationship with some tables and you want to select just one record from the many-table then it can be a bit of a fiddle.

There is an easy way to do it (well, in MS-SQL) using the row_number() function.

Consider the following structure:

CREATE TABLE #customer (	customer_id int PRIMARY KEY,customer_name varchar(32))

CREATE TABLE #orders (
	order_id int IDENTITY PRIMARY KEY,
	order_customer_id int REFERENCES customer(customer_id),
	order_description varchar(32),
	order_date smalldatetime)

With some data:

INSERT INTO #customer VALUES (1, 'Bob')
INSERT INTO #orders VALUES (1, 'Widgets', getdate())
INSERT INTO #orders VALUES (1, 'Boxes', getdate()-1)

INSERT INTO #customer VALUES (2, 'Bill')
INSERT INTO #orders VALUES (2, 'Boxes', getdate()-1)
INSERT INTO #orders VALUES (2, 'Widgets', getdate())

And the following objective:
Display the customer record along with their most recent order

You may follow the route of

SELECT * FROM #customer c LEFT JOIN #orders o ON c.customer_id = o.order_customer_id ORDER BY o.order_date DESC

The problem with that route is you get multiple instances of each customer for each order that exists. If you think out to a bigger example you may have hundreds of rows for each customer.

You can though, use the aforementioned ROW_NUMBER() function to produce a sequential number next to each record. This can be ordered and then you merely need to do a where clause against the row_number column to get 1 (or as many as you fancy) records. e.g.

SELECT * FROM #customer c LEFT JOIN (
    SELECT 
        row_number() OVER (
            PARTITION BY order_customer_id 
            ORDER BY order_date DESC) AS [order_rank]
        , *
    FROM #orders
) AS o ON c.customer_id = o.order_customer_id
WHERE o.order_rank = 1