I've database with two tables:
Customers (Id PK, LastName)
Orders (Id PK, CustomerId FK, ProductName, Price, etc.)
I wish to retrieve only customer' last orders particulars along with customer title. I personally use .Internet L2SQL however i think it's SQL question a lot more than LINQ question and so i publish here SQL query I attempted:
SELECT [t0].[LastName], ( SELECT [t2].[ProductName] FROM ( SELECT TOP (1) [t1].[ProductName] FROM [Orders] AS [t1] WHERE [t1].[CustomerId] = [t0].[Id] ORDER BY [t1].[Id] DESC ) AS [t2] ) AS [ProductName], ( SELECT [t4].[Price] FROM ( SELECT TOP (1) [t3].[Price] FROM [Orders] AS [t3] WHERE [t3].[CustomerId] = [t0].[Id] ORDER BY [t3].[Id] DESC ) AS [t4] ) AS [Price] FROM [Customers] AS [t0]
Issue is that Orders has more posts (30) with each column the query will get bigger and reduced because I have to add next subqueries.
Can there be much better way?
SQL Server 2005 and above:
SELECT * FROM ( SELECT o.*, ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY o.id DESC) rn FROM customers c LEFT JOIN orders o ON o.customerId = c.id ) q WHERE rn = 1
SELECT * FROM customers c OUTER APPLY ( SELECT TOP 1 * FROM orders o WHERE o.customerId = c.id ORDER BY o.id DESC ) o
SQL Server 2000:
SELECT * FROM customers с LEFT JOIN orders o ON o.id = ( SELECT TOP 1 id FROM orders oi WHERE oi.customerId = c.id ORDER BY oi.id DESC )
Don't you've got a date of column within the orders table something similar to order_date? Rather than choose top you need to have the ability to retrieve using max(order_date)
My sql is rusty but something similar to this
select c.col1,o.col1,o.col2,o.col3 from Customers as c, Orders as o where c.id = o.customerid and max(o.order_date)