August 27, 2014 at 1:33 am
I have a problem that I've been using what I call a "clumsy" workaround for so far, but would like to know if someone else has an obvious answer.
I have created the following SQL snippet that is a very simple mock-up illustrating the problem (I hope!) that I am facing:
-- create table
if object_id('tempdb..#tmpdelnotes') is not null
drop table #tmpdelnotes
create table #tmpdelnotes(
DelNote int identity (1,1) ,
DelDate date not null,
Item int not null,
Customer int not null)
-- populate table
insert into #tmpdelnotes values
('20140821',10,1),
('20140829',10,1),
('20140730',20,1),
('20131231',30,2),
('20140601',10,3)
-- select ordered data (full set for visibility)
select customer, item, delnote, deldate, row_number() over (partition by item order by deldate desc) as rn
from #tmpdelnotes
-- "clumsy" workaround to get a table with unique "Item"
select t1.customer, t1.item, t1.delnote, t1.deldate
from (
select item, customer, delnote, deldate, row_number() over (partition by item order by deldate desc) as rn
from #tmpdelnotes
) t1
where t1.rn=1
What I need to retrieve is a unique list of item numbers with information about the latest (DelDate) delivery note. The "Clumsy workaround" works, but is not very pretty when doing multiple table joins. Is it really necessary to use a derived table for this kind of query? Window functions can only exist in the SELECT and ORDER BY clauses, which is understandable since the calculations take place (I would guess) after the aggregations in the HAVING clause.
August 27, 2014 at 5:53 am
There are a few alternatives, such as this:
SELECT MyStuff
FROM TableList t
OUTER APPLY (
SELECT TOP 1 item, customer, delnote, deldate
FROM #tmpdelnotes d
WHERE d.Item = t.Item
ORDER BY deldate DESC
) ou
Can you provide an example query?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 27, 2014 at 6:29 am
I don't think your approach is clumsy, in fact if you add a POC (Partition Order Cover) index on the table, this method is very hard to beat!
😎
-- create table
if object_id('tempdb..#tmpdelnotes') is not null
drop table #tmpdelnotes
create table #tmpdelnotes(
DelNote int identity (1,1) ,
DelDate date not null,
Item int not null,
Customer int not null)
-- populate table
insert into #tmpdelnotes values
('20140821',10,1),
('20140829',10,1),
('20140730',20,1),
('20131231',30,2),
('20140601',10,3);
CREATE NONCLUSTERED INDEX TEMP_DEL_NOTES_POC_LAST_VALUE ON #tmpdelnotes (item ASC, deldate DESC) INCLUDE (DelNote,Customer)
-- select ordered data (full set for visibility)
select customer, item, delnote, deldate, row_number() over (partition by item order by deldate desc) as rn
from #tmpdelnotes
-- "clumsy" workaround to get a table with unique "Item"
select t1.customer, t1.item, t1.delnote, t1.deldate
from (
select item, customer, delnote, deldate, row_number() over (partition by item order by deldate desc) as rn
from #tmpdelnotes
) t1
where t1.rn=1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply