November 28, 2012 at 12:09 pm
Hi,
I have customer table and order table and both consists of customerID column
which is primary key for customer table.
I want to write a query which we will give all the recent order for each customer but restricting to the most recent order only.
For Example:
Customer Table:
CustomerID
1
2
3
Order Table
OrderID CustomerID OrderDate Amount
1 1 2012-11-27 300
2 1 2012-11-28 350
3 2 2012-11-28 400
Result Set Needed:
CustomerID OrderDate Amount
1 2012-11-28 350
2 2012-11-28 400
NOTE: I need this T-SQL query without using CTE or Ranking Function like RANK(), ROW_NUMBER(),etc...
Thank you in advance.
November 28, 2012 at 12:14 pm
NOTE: I need this T-SQL query without using CTE or Ranking Function like RANK(), ROW_NUMBER(),etc...
This sound like homework with this caveat. This can be done with them. We used to have to do this kind of stuff all the time in 2000. It is horrible to write and performance is not good. Then along came the ranking functions and the sql world rejoiced. You posted this in the 2012 forum but want to use 2000 technology to solve it. This is like asking us to calculus using an abacus. Sure it can be done but why not use a better tool/technique for the job?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2012 at 12:22 pm
Hey,
While working with Ranking Function it occurred me that how can I re-write this query without using Ranking function. I am not able to come up with any logic without using Ranking function.
Thank you.
Regards.
November 28, 2012 at 12:25 pm
monilps (11/28/2012)
Hey,While working with Ranking Function it occurred me that how can I re-write this query without using Ranking function. I am not able to come up with any logic without using Ranking function.
Thank you.
Regards.
So you did or did not figure out a way to do it? Is this just for fun?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2012 at 12:40 pm
I decided that just for fun I would put together a few different ways of the same query.
The first two do not use a cte or ranking function. I would not use either of those anymore but they will work.
if object_id('tempdb..#Customer') is not null
drop table #Customer
if object_id('tempdb..#Order') is not null
drop table #Order
create table #Customer
(
CustomerID int
)
create table #Order
(
OrderID int,
CustomerID int,
OrderDate datetime,
Amount int
)
insert #Customer
select 1 union all
select 2
insert #Order
select 1, 1, '2012-11-27', 300 union all
select 2, 1, '2012-11-28', 350 union all
select 3, 2, '2012-11-28', 400
--Version #1
select c.CustomerID, o.OrderDate, o.Amount
from #Customer c
join #Order o on o.CustomerID = c.CustomerID
where o.OrderID = (select top 1 OrderID from #Order where CustomerID = c.CustomerID order by OrderDate desc)
--Version #2
select *, (select top 1 OrderDate from #Order where CustomerID = c.CustomerID order by OrderDate desc) as OrderDate,
(select top 1 Amount from #Order where CustomerID = c.CustomerID order by OrderDate desc) as Amount
from #Customer c
--Version #3 cte
;with cte as
(
select *, ROW_NUMBER() over (PARTITION BY CustomerID order by OrderDate desc) as RowNum
from #Order
)
select CustomerID, OrderDate, Amount
from cte
where RowNum = 1
--Version #4 subquery
select CustomerID, OrderDate, Amount from
(
select *, ROW_NUMBER() over (PARTITION BY CustomerID order by OrderDate desc) as RowNum
from #Order
)x where RowNum = 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2012 at 2:01 pm
I typically use one of the ranking function techniques like ROW_NUMBER(), but my favorite alternative to that if you're using SQL 2005 or newer is the CROSS APPLY:
SELECT c.CustomerID, o.OrderDate, o.Amount
FROM #Customer c
CROSS APPLY
(SELECT TOP 1 o2.OrderID, o2.OrderDate, o2.Amount
FROM #Order o2
WHERE o2.CustomerID = c.CustomerID
ORDER BY OrderDate DESC) o
it works especially well if there's an index on the #Order table on (CustomerID, OrderDate DESC)
December 12, 2012 at 9:42 pm
December 17, 2012 at 10:59 am
Keep in mind that for cross apply with top (n) to outperform the use of ranking functions, you have to have an index available that is already sorting the data by the same field(s) as your order by clause. Otherwise, just like your ranking functions, every record would need to be touched and you would receive no performance benefit.
December 18, 2012 at 8:08 am
another option. particularly effective if there is an index on the column in the contraint. Customerid in this instance. Prob better than the cross apply though.
SELECT c.CustomerID, o.OrderDate,
( SELECT TOP 1 o2.Amount Amount
FROM #Order o2
WHERE o2.CustomerID = c.CustomerID
ORDER BY o2.OrderDate DESC
) Amount
FROM #Customer c
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply