That condition cannot happen per our design.

Hi Sergiy, thats condition # 2, isnt it?

Eirikur, thanks for the reply. I am still not convinced.

I have added a condition PreviousdayClose <> 0 in CTE1 to exactly handle situations like this, where LEAD is seeding...

Thanks, Erland. That makes sense. But are you aware of any way to work around it?

My requirement:

We have 2 database servers that are replicated via third party replication technology....

Thanks for the advices and tips guys. Regarding indexes, i have tried all sorts of indexes (filtered/covered/clustered/non-clustered), and have optimized the query. But I was more interested to know if...

Jeff Moden (6/13/2014)

Heh... apparently, you didn't read the article where is says...

The code above isn't adequate for...

Now that we have new analytical functions, just wanted to jump in on the new windowed functions fun

; WITH CTE AS

(

SELECT P.CustomerID ,P.ProductCode

...

Do we have a readymade 10 million/1million row generator for this problem?

Can you just remove the T.Cid form the THEN clause and replace it with 0 (zero) and try?

ORDER BY CASE WHEN COUNT(T1.CId) = 1 THEN 0

ELSE COUNT(T1.CId)

...

My query, without chaning anything, works for tie-breakers too as per your rule (highest of cid should be retrieved). Can you give me a perfect set of example to work...

You dint understand the part ONLY with the count(*)/count(T1.CiD) or the entire query?

Count(*) yielded the number of rows for CNo. But Count(T1.CiD) will yield the number of rows present in...

I see your point now

; WITH RankedListed (CNo, CId, [Rank]) AS

(

SELECT T.CNO

,T.CId

,ROW_NUMBER()

OVER ( PARTITION BY...

My code exactly does that. Does it not? Can you please tel me what error you are seeing with my code?

Whoa.. with 542 posts, you still dont know how to post questions which will yeild best possible replies?

Please read these links and post your question more legibly:

Forum etiquette[/url]

How to Post...

How about this?

; WITH RankedListed (CNo, CId, [Rank]) AS

(

SELECT T.CNO

,T.CId

,ROW_NUMBER()

OVER ( PARTITION BY T.CNO

...