question

create table test1 (CityName varchar(20),DayName varchar(4),Occurance int) insert into test1 values('Newyork','Mon',0) insert into test1 values('Newyork','Mon',0) insert into test1 values('Newyork','Tues',0) select Cityname,dayname,Occurance from (select test1.Cityname,test1.dayname,-1 as Occurance from test1 union select cityname,dayname,count(Cityname) as Occurance from test1 group by Cityname,dayname) temp1 where Occurance<>-1

Perhaps something like this? CREATE TABLE test1 ( CityName VARCHAR(20) , DayName VARCHAR(4) , Occurance INT , DayTime SMALLDATETIME ); INSERT INTO test1 VALUES ( 'Newyork', 'Mon', 1, '2016-01-01' ); INSERT INTO test1 VALUES ( 'Newyork', 'Mon', 2, '2016-01-15' ); INSERT INTO test1 VALUES ( 'Newyork', 'Mon', 3, '2016-01-22' ); INSERT INTO test1 VALUES ( 'Newyork', 'Tues', 1, '2016-01-02' ); ; WITH cte_Occurance AS ( SELECT CityName , DayName , MAX(Occurance) AS 'MaxOccurance' FROM test1 GROUP BY CityName , DayName ) SELECT T.CityName , T.DayName , T.Occurance , T.DayTime FROM cte_Occurance AS CO INNER JOIN test1 AS T ON T.CityName = CO.CityName AND T.DayName = CO.DayName AND T.Occurance = CO.MaxOccurance;

I could certainly be incorrect here but, because of the wording of the original question, I think this problem may have gone in the wrong direction. So, here's my take on the problem. Most importantly, I believe that the "Occurrence" column isn't actually in the original data. I believe the "Occurrence" column is the desired result. Using that assumption, this is nothing more than a partitioned row number problem. Using that perhaps incorrect interpretation of what the OP is stating, here's a solution for that interpretation. --===== If the test table already exists, drop it to make reruns -- in SSMS easier. IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable ; GO --===== Create the test table and populate it with data on-the-fly. -- This is NOT a part of the solution. -- This is just to demonstrate the solution. SELECT * INTO #TestTable FROM ( SELECT 'NEWYORK','MON' UNION ALL SELECT 'NEWYORK','MON' UNION ALL SELECT 'NEWYORK','MON' UNION ALL SELECT 'NEWYORK','TUE' UNION ALL SELECT 'CHICAGO','MON' UNION ALL SELECT 'CHICAGO','MON' ) d (City,Day) ; --===== Here's what I think the solution is. SELECT City ,DAY ,Occurrence = ROW_NUMBER() OVER (PARTITION BY City,Day ORDER BY City,Day) FROM #TestTable ORDER BY City,Day ; Here are the results. The sort order is different because the original data wasn't sorted by city but the results are. City DAY Occurrence ------- ---- -------------------- CHICAGO MON 1 CHICAGO MON 2 NEWYORK MON 1 NEWYORK MON 2 NEWYORK MON 3 NEWYORK TUE 1 (6 row(s) affected)