December 31, 2012 at 1:29 am
Hi All,
Please can someone tell me how to select min/max rows in a single query similar to the one below.
Select custfirst, custlast,
first_order_id,
first_orderdate,
first_order_total_items,
first_order_total_cost
last_orderdate,
last_order_id,
last_orderdate,
last_order_total_items,
last_order_total_cost
from custmer where
custid in (select max(orderdate) as last_orderdate from orders group by custid, orderdate order by orderdate DESC) OR
custid in (select min(orderdate) as first_orderdate from orders group by custid, orderdate)
Thanks in advance
December 31, 2012 at 1:58 am
Does the OrderId column follow an ascending pattern? What I mean is, can we assume that, for each customer,
Min(OrderId) is the first order
and
Max(OrderId) is the most recent order
?
Also, if a customer has only one order, would you like to see it repeated as both first and last?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 31, 2012 at 2:29 am
OrderID is auto gen number but can use orderdate for this purpose.
If there is one order placed, then I want both min/max to contain the same long with rest of the data belongs to min/max rows
December 31, 2012 at 2:51 am
popq79 34287 (12/31/2012)
OrderID is auto gen number but can use orderdate for this purpose.If there is one order placed, then I want both min/max to contain the same long with rest of the data belongs to min/max rows
OK, this is a bit ugly (and untested) but should get you on the right track:
with FOrders
as (
select CustId
,Ro = Row_Number() over (
partition by CustId order by OrderDate asc
)
,FirstOrderId = OrderId
,FirstOrderDate = OrderDate
)
,LOrders
as (
select CustId
,Ro = Row_Number() over (
partition by CustId order by OrderDate desc
)
,LastOrderId = OrderId
,LastOrderDate = OrderDate
)
select c.CustId
,FOrders.FirstOrderDate
,FOrders.FirstOrderId
,LOrders.LastOrderDate
,LOrders.LastOrderId
,
from Customer c
left join FOrders on c.CustId = FOrders.CustId
left join LOrders on c.CustId = LOrders.CustId
where (
FOrders.ro = 1
or FOrders.ro is null
)
and (
LOrders.ro = 1
or LOrders.ro is null
)
--Edit fixed row_number() clauses
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 1, 2013 at 8:31 pm
Won't something like this work also?
CREATE TABLE #Customer
(CustID VARCHAR(10)
,OrderDate DATETIME)
INSERT INTO #Customer
SELECT 'A', '2012-01-31' UNION ALL SELECT 'A', '2012-03-31' UNION ALL SELECT 'A', '2012-05-15'
UNION ALL SELECT 'B', '2012-03-31' UNION ALL SELECT 'B', '2012-05-31' UNION ALL SELECT 'B', '2012-07-15'
SELECT CustID, OrderDate -- Include other fields you want returned here
FROM (
SELECT CustID, OrderDate -- Include other fields you need here
,a=MIN(OrderDate) OVER (PARTITION BY CustID)
,b=MAX(OrderDate) OVER (PARTITION BY CustID)
FROM #Customer) a
WHERE a = OrderDate OR b = OrderDate
DROP TABLE #Customer
Happy New Year!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply