December 29, 2011 at 1:27 pm
Hello All,
I have a query like this
select [ACCOUNT_NUMBER]
,[End_Date]
,[LDC_Account_Num]
,ROW_NUMBER() over (partition by account_number order by end_date asc ) orgcnt
from dbo.esg_ldc_xref
where customer_tkn in ( 56134996 , 90640028, 92448615)
order by account_number
I am getting the result as
561 2011-11-28 111223
561 2011-11-29 123456
562 2011-11-16 56789
562 2011-11-17 78906
But I want the result as
561 2011-11-29 123456
562 2011-11-17 78906
Taking only the records that have maximum of end date partition by account number. What I need to modify in the above query to get this result?.
Please assist
Thanks
December 29, 2011 at 1:40 pm
I'd query each account number, and then cross apply to get the date with the most records for each.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 29, 2011 at 1:40 pm
how about replacing:
over (partition by account_number order by end_date asc )
by:
top 1 over (partition by account_number order by end_date desc)
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 29, 2011 at 1:50 pm
I wouldn't do this with Row_Number, I'd approach it this way:
SELECT
elx.*
FROM
dbo.esg_ldc_xref AS elx
JOIN
(SELECT
Account_Number,
MAX(End_Date) AS MaxEndDate
FROM
dbo.esg_ldc_xref
GROUP BY
Account_number
) AS drv
ONelx.Account_number = drv.Account_number
AND elx.end_date = drv.End_Date
WHERE
customer_tkn in ( 56134996 , 90640028, 92448615)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 29, 2011 at 1:57 pm
A bunch of good ways to do it. Yours looks really close. Seems all you would have to do is throw it in a CTE and then do a select where orgcnt = 1.
December 29, 2011 at 2:46 pm
Thanks so much. That worked. I used Max function
December 29, 2011 at 3:12 pm
Evil Kraig F (12/29/2011)
I wouldn't do this with Row_Number
I would use Row_Number, because using MAX() in a subquery has the possibility of duplicate values if there are multiple records with the same MAX() value whereas the ROW_NUMBER() option does not.
I also think that the ROW_NUMBER is more efficient, but don't have time to test it now. (I don't want to miss my train.)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 29, 2011 at 3:31 pm
drew.allen (12/29/2011)
Evil Kraig F (12/29/2011)
I wouldn't do this with Row_NumberI would use Row_Number, because using MAX() in a subquery has the possibility of duplicate values if there are multiple records with the same MAX() value whereas the ROW_NUMBER() option does not.
I also think that the ROW_NUMBER is more efficient, but don't have time to test it now. (I don't want to miss my train.)
Drew
Best option for that, actually, is the Cross Apply Top 1 query. row_number() under most circumstances is the least efficient means of getting the last entry per group in a logging table. I personally want to see dupes unless they're 'true dupes', because I would rather leave it on the user to decide which was was the correct entry instead of building in rediculous logic to avoid it.
If you'd like an incomplete test bed for reviewing the efficiency of the methods, here's a script I've been using as a testing mechanism for just that: http://www.sqlservercentral.com/Forums/Attachment10272.aspx
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 30, 2011 at 1:12 am
PaulB-TheOneAndOnly (12/29/2011)
how about replacing:over (partition by account_number order by end_date asc ) by:
top 1 over (partition by account_number order by end_date desc)
That would be cool, but SQL Server does not support a windowing clause with TOP.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 30, 2011 at 1:20 pm
select [ACCOUNT_NUMBER]
,[End_Date]
,[LDC_Account_Num]
from
(select [ACCOUNT_NUMBER]
,[End_Date]
,[LDC_Account_Num]
,ROW_NUMBER() over (partition by account_number order by end_date desc ) orgcnt
from dbo.esg_ldc_xref
where customer_tkn in ( 56134996 , 90640028, 92448615)
) q
where orgcnt=1
order by account_number
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply