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