Hi,

I am fairly new to T-SQL although I would not classify myself as a complete
beginner.

Last week I came across a strange business requirement for an application,
and I am still trying to figure out how to address it in T-SQL. I need to
pull a list of each customer's last order for a specific date (easy to do),
as well as set a flag on every *other* order that is not the last order for
that day (not so easy to do).

Although I can easily pull the last order per customer per day using the
MAX() aggregate function in a subquery, I cannot figure out how to go about
updating every other record via a single query. So I have to loop through
the list of all orders one by one and update each row manually. Obviously
that is less than ideal and I would rather do a single (or two) rowset
expression(s).

Here is how I am currently pulling the orders:

SELECT Customer.Name, Order.OrderAmount
FROM Customer LEFT OUTER JOIN Order ON Customer.CustomerID =
Order.CustomerID
WHERE Order.OrderID IN (
SELECT MAX(Order.OrderID)
FROM Order
GROUP BY Order.CustomerID, Order.OrderDate
)


How would you suggest I proceed? I hope this makes sense, but let me know if
I can clarify this in any way.

Thanks in advance!

Regards,
George

George,

Untested:
SELECT Customer.Name, Order.OrderAmount,
(SELECT COUNT(*) FROM Order o1 WHERE o1.CustomerID = c.CustomerID AND
o1.OrderDate = o.OrderDate AND o1.OrderID > o.OrderID)
AS HasLaterOrders
FROM Customer c LEFT OUTER JOIN Order o ON c.CustomerID =
o.CustomerID

-----------------------
Alexander Kuznetsov
http://sqlserver-tips.blogspot.com/

First of all, consider replacing max(Order.OrderID) with an expression that
more closely follows the business requirement - e.g.
max(Order.OrderProcessedDateTime) if such a column exists.

Second, are you aware of what this condition actually evaluates to?

WHERE Order.OrderID IN (
SELECT MAX(Order.OrderID)
FROM Order
GROUP BY Order.CustomerID, Order.OrderDate
)

This has little to do with what you've described in your post. Long story
short - there is only a weak correlation between the main query and the
subquery. The subquery returns a list of all highest OrderID values for a
particular CustomerID, and while this may serve your purposes, it would be
better to find a more solid common ground for the two sets.

Since you haven't provided any DDL or sample data, it's difficult to come up
with an exact answer that would help you solve your problem. Please, read
this article:
http://www.aspfaq.com/etiquette.asp?id=5006


Anyway, consider these facts:

1) You need a fully reliable means of determining "the last order per
customer";

2) You need to understand why a specific row should be "flagged", what it
would take to maintain this functionality, and what possible consequences may
arise if this functionality is not properly maintained (i.e. the same could
be achieved through the use of a view, without the need to "flag" rows).



ML

---

I trust that by update you mean within the result set, not the base
table. As written the query posted will act as an INNER join, not
LEFT OUTER, because of the test on the right-hand table in the WHERE
clause. Also worth noting is that ORDER is a reserved word and a
terrible choice for a table name; a common alternative is to use the
plural, Orders.

You might try something along these lines:

SELECT Customer.Name, Orders.OrderAmount,
CASE WHEN Orders.OrderID =
(select max(OrderID)
from Orders as X
where X.CustomerID = O.CustomerID
and <same date range test against X>)
THEN 'last'
ELSE 'not last'
END as LastOrNot
FROM Customer
LEFT OUTER
JOIN Orders
ON Customer.CustomerID = Orders.CustomerID
AND <date range test against Orders>

Roy Harvey
Beacon Falls, CT

On Mon, 18 Sep 2006 06:58:11 -0700, "George Shawn" <george@nospam.com>
[quoted text, click to view]