August 17, 2011 at 9:39 am
Sure.
SELECT
d.[rec_index],
d.file_desc,
d.is_modified
FROM (
SELECT
[rec_index],
file_desc,
is_modified,
rn = ROW_NUMBER() OVER(PARTITION BY file_desc ORDER BY rec_index DESC)
FROM MyTable
) d WHERE d.rn = 1 AND d.is_modified = 0
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 17, 2011 at 9:57 am
Heh! Funny - I thought your solution had a triangular join but it isn't. That >= ALL () appears to be very efficient with this small data set. It's twice as fast as my solution and the nearest logical alternative to yours:
select *
from (
select *
from #MyTable as t2
where rec_index = (
select MAX(rec_index)
from #MyTable as t1
where t2.file_desc = t1.file_desc)
) as t3
where t3.is_modified = 0
Definitely worth scaling this up to a proper test.
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
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply