Your table F07FcstSlsRepDomesticTbl_tmp may be haing these columns.

CrntSubFcst,F06Flag, S07Flag, F07Flag

Avoide these in group by and you should be okay.

Thanks Sergy.

I was trying all data types and just found Real and float works.

Thanks a million Jeff and PW. That was really very helpful.

It is SQL2K.

Thanks David. Good idea. I will use your idea.

I did like this which almost solved my work.

SELECT A.ItemName, A.ItemValue

FROM

 @MyTable A

JOIN

 (

 SELECT ItemName, MAX(ItemValue) ItemValue

 FROM

  @MyTable

 GROUP BY ItemName) B

ON

 A.ItemName = B.ItemName

AND A.ItemValue = B.ItemValue

GROUP BY...

First items are not stored any where.

I have data like  gave in the sample. And I have to get a result set as I mentioned in the original post.

To be...

Sergiy

ItemValue is the deciding factor here. I have to select the second highest value for each item. I can do that.

Where I am getting stuck is if highest value is shared...

Item Number does not matter.

It is like recruitng top student from each class(Item). (First one is already gone so take second). If all the students scored same any one.

I have to wait for about an year (hope) to use CTE.

Remi 10 minutes took 4 days for you. I was wondering at that point no one even read the...

I have similar table with int column clustered index and date column non clusterd index with about 5 million rows. With are without date criteria it returns data in millseconds.

And...

I don't believe this so for no one even read this post. No one like Remi is hanging around any more??

I did it like this. I would like to know...

Thanks Vldan. That answered my question.

Sorry. That was copy paste problem.

Contact ID is primary Key.

Data corrected in original post.

I never like to use IN or NOT IN. I will try to avoid it following way. This aprocah always helped me to improve the performance.

SET NOCOUNT ON

DECLARE @MainTable TABLE

(

MyValue...

Sorry I did not check the code before posting. Good you figured it 

HTH.