Pick the row with max count on each day

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pick the row with max count on each day

Author

Topic

daman
Yak Posting Veteran

72 Posts

Posted - 2007-10-25 : 15:39:39

Hi everyone,
This is my first post and I hope to learn much from this community.

I've been learning SQL 2005 the last few weeks and I encounter a simple problem here that I hope experienced members
can provide some input

I have a table called Reference with the column named as below. The Count and Date column are ordered ASC

Level Count Date
49 2 3/20/2007
37 65 3/20/2007
48 3 3/21/2007
36 40 3/21/2007
47 1 3/22/2007
35 25 3/22/2007
47 4 3/23/2007
35 22 3/23/2007
47 3 3/26/2007
35 35 3/26/2007

I would like to write a stored procedure to go through each row and only select the row with the max value in
Count column for each day. For example, there are 2 rows for 3/20/2007 and I would like to pick the one with 65 count instead of 2

SPECIAL NOTE: This can be added in later if it's too complicated for now. For the time being, just ignore this.
If a day has multiple rows with equal counts, select the last row or the row with the level close to the level of the day before or the day after

Here is my desired resulting table

Level Date
37 3/20/2007
36 3/21/2007
35 3/22/2007
35 3/23/2007
35 3/26/2007

Many thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-25 : 16:53:15

[code]
Declare @T Table ([Level] int, [Count] int, Date datetime)
Insert into @T
Select 49, 2, '3/20/2007' union all
Select 37, 65, '3/20/2007' union all
Select 48, 3, '3/21/2007' union all
Select 36, 40, '3/21/2007' union all
Select 47, 1, '3/22/2007' union all
Select 35, 25, '3/22/2007' union all
Select 47, 4, '3/23/2007' union all
Select 35, 22, '3/23/2007' union all
Select 47, 3, '3/26/2007' union all
Select 35, 35, '3/26/2007'

Select T.*
from @T T
Join ( Select max([count]) MCount, Date
FROM @T T1
GROUP BY T1.Date
) T2 ON T.Date = T2.Date And T.[Count] = T2.MCount
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

daman
Yak Posting Veteran

72 Posts

Posted - 2007-10-25 : 17:07:15

Really appreciate your time and input.

The table is 100K rows so I don't think I use your method.

When I tried this
select max(Count),date from reference
group by quotedate

I have this result

65 2007-03-20
40 2007-03-21
25 2007-03-22
22 2007-03-23
35 2007-03-26

Would be great if i can somehow get the Level out of this also ?
Would you be able to use subquery, select inside select to pull this out ?

daman
Yak Posting Veteran

72 Posts

Posted - 2007-10-25 : 18:26:35

Dinakar,

I was able to get what I want from your post. Thanks so so much

Select T.entityId,T.term,T.reference,T.QuoteDate
from Reference T
Join ( Select max(occurNumber) ONumber, QuoteDate
FROM Reference T1
GROUP BY T1.QuoteDate
) T2 ON T.QuoteDate = T2.QuoteDate And T.[occurNumber] = T2.ONumber
order by T.QuoteDate

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 02:15:31

Since you are using SQL Server 2005...

Declare @T Table ([Level] int, [Count] int, [Date] datetime)
Insert into @T 
Select 49, 2, '3/20/2007' union all
Select 37, 65, '3/20/2007' union all
Select 48, 3, '3/21/2007' union all
Select 36, 40, '3/21/2007' union all
Select 47, 1, '3/22/2007' union all
Select 35, 25, '3/22/2007' union all
Select 47, 4, '3/23/2007' union all
Select 35, 22, '3/23/2007' union all
Select 47, 3, '3/26/2007' union all
Select 35, 35, '3/26/2007'

SELECT	[Level], [Count], Date FROM (
SELECT [Level], [Count], Date, ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY [Count] DESC) AS RecID
FROM @T) AS d WHERE RecID = 1

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources