Getting One Record Per Month
-
How do I write a query to only return one record per month, the record with the highest (max) number of session_count.
Here is the table description:
Create table stats (stat_date datetime, session_count int,……..)
This table has around 500,000 rows
What I want is to return only one record per month, and I want it to show the actual stat_date. Plus I don’t want it to run for every.
I can easily get the highest session_count count by doing the following:
Select convert (char(7),stat_date,111), max(session_count) from
Stats
Group by convert(char(7),stat_date,111)
But I would like the actual date and time when the max(session_count) occurred. I
Could also do the following but this might return more than one row per month, should multiple records have the same max user_connections count:
select b.stat_date, a.session_count from
(Select convert (char(7),stat_date,111) yyyymm, max(session_count) session_counts from
stats
Group by convert(char(7),stat_date,111)) a join dba.dbo.performance_info b
on yyyymm = convert(char(7),b.stat_date,111) and a.session_counts = b.session_counts
How can I get only one record per month, with the actual date/time.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Greg, have you thought about using the TOP or SET ROWCOUNT command? Not sure if it matters what record you get, but this will ensure only one record is returned.
Greg Robidoux
Edgewood Solutions
http://www.edgewoodsolutions.com
Greg Robidoux
Edgewood Solutions
www.edgewoodsolutions.com -
I have thought about the top 1, but am not sure how they might be used to return one row per month. Note my data has many months worth of data, and I would like the top 1 returned for each month Do you have some suggestions on how I might use top 1 or set rowcount to accomplish this? If I only had one months worth of data then top 1 would work fine. But when I have multiple months, I'm just drawing a blank on how to do this in a single TSQL command.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
/* Hope this helps */
Select t.*
From MyStats t
Inner join (Select Convert(Char(7),Stat_Date,111),Max(Session_Count)
From MyStats
Group by Convert(Char(7),Stat_Date,111)) as f(lMonth,Session_Count)
On Convert(Char(7),t.Stat_Date,111)=f.lMonth And t.Session_Count=f.Session_Count
-- But limit to where yyyy/month and session = max(session)
Where Convert(Char(7),t.Stat_Date,111)=f.lMonth And t.Session_Count=f.Session_Count And
-- Of these multi record take the one with the min stat_date
t.Stat_Date=(Select Min(Stat_Date)
From MyStats t2
Where Convert(Char(7),t2.Stat_Date,111)=f.lMonth And
t2.Session_Count=f.Session_Count)
-
Your query worked fine, thank you for the code. I also solved my problem using the following:
select c.stat_date, c.session_count from
(select b.stat_date, a.session_count
from (select convert(char(7),stat_date,111) as Year_Month,
max(session_count) as session_count
FROM stats
GROUP BY
convert(char(7),stat_date,111)
)a
join dba.dbo.performance_info b
on a.Year_Month = convert(char(7),b.stat_date,111)
and a.session_count = b.session_count) c
where c.stat_date = (select top 1 stat_date from stats
where convert(char(7),c.stat_date,111) = convert(char(7),stat_date,111)
order by session_count desc)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Then it's a "min" versus a "top 1 order by desc"
If you have time, which IO is the the least expensive.
(I also like Joe Celko smarties)
-
Both ran relatively the same length of elpased time, but I should do the test with statistics IO on.
Also isn't there a command I need to run test to flush the buffers between test? Can't seem to remember what it is. Does anyone know if I'm dreaming here, or is there a command I should run between tests.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Two commands:
DBCC FREEPROCCACHE
This one clears the execution plan cache. Even ad-hoc queries are in there, so clear it.
DBCC DROPCLEANBUFFERS
This one clears the memory cache of data.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Thank you for both these. I will do some more testing soon if I can find time.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
This is an example of where a self join can help.
select * from table1 outside
where crdate =
(select min(crdate)
from table1 inside
where datepart(yyyy, outside.crdate) = datepart(yyyy, inside.crdate)
and datepart(MM, outside.crdate) = datepart(mm, inside.crdate)
)
If you want the latest record each month, use max() instead of min().
-
This is an example of where a self join can help.
select * from table1 outside
where crdate =
(select min(crdate)
from table1 inside
where datepart(yyyy, outside.crdate) = datepart(yyyy, inside.crdate)
and datepart(MM, outside.crdate) = datepart(mm, inside.crdate)
)
If you want the latest record each month, use max() instead of min().
-
This is an example of where a self join can help.
select * from table1 outside
where crdate =
(select min(crdate)
from table1 inside
where datepart(yyyy, outside.crdate) = datepart(yyyy, inside.crdate)
and datepart(MM, outside.crdate) = datepart(mm, inside.crdate)
)
If you want the latest record each month, use max() instead of min().
-
This is an example of where a self join can help.
select * from table1 outside
where crdate =
(select min(crdate)
from table1 inside
where datepart(yyyy, outside.crdate) = datepart(yyyy, inside.crdate)
and datepart(MM, outside.crdate) = datepart(mm, inside.crdate)
)
If you want the latest record each month, use max() instead of min().
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply