SQLTeam.com | Weblogs | Forums

T-sql 2012 pick most current record


#1

In a sql server 2012 database, I have the following 2 tables:
1.
CREATE TABLE [dbo].[Orders](
[orderID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CustID] [int] NOT NULL,
[comments] varchar NULL,
[PrintedDate] [datetime] NULL
)
2.
CREATE TABLE [dbo].[Customer](
[CustID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[laddress] varchar NULL,
[modifiedByID] [int] NOT NULL
)

I want to join the customer table to the orders table and select the most current printdate for a customer
from the orders table using a where clause potentially and not using the max(PrintedDate) on a select statement.
There should only be 1 record from the orders table selected.
I would like to accomplish this goal due to addtional tables that I will need to add to the l sql
that I listed here.

Thus the start of the sql would be:

select Customer.CustID,Orders.CustID,Orders.PrintedDate
from Customer as Customer
join Orders as Orders
on Orders.CustID = Customer.CustID
where Orders.PrintedDate is the most current date?

Thus would you should be how to have only the record from the orders table with the most
current print date using potential:
a. where clause,
b. having clause,
c. cte,
d. temp table,
and/or any other option you think would work and not using the max(PrintedDate) on a select statement?


#2

I would use CROSS APPLY...

SELECT ...
  FROM dbo.Customers c
 CROSS APPLY (SELECT TOP 1 
                     ... 
                FROM dbo.Orders o 
               WHERE o.CustID = c.CustID 
               ORDER BY o.PrintedDate desc) As ord
 WHERE ...