SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Selecting both MIN and MAX in Single SQL Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jbates99
Constraint Violating Yak Guru

379 Posts

Posted - 08/20/2013 :  17:14:18  Show Profile  Reply with Quote
Hi everyone,

I'm trying to select the smallest size for each database and the date for that size.

The code below returns all dates that have been collected.
I need to get 1 or 2 rows for each DBName. Something like...

Server1 master 1-1-2013 25 MB
Server1 master 7-12-2013 28 MB

Can this be done without using temp tables?
Thanks, Jack


select ServerName, DBName, AsOfDate
, MIN(FileUsedMB) as MinimumSize
, MAX(FileUsedMB) as MaximumSize
from DBSize
where FileType = 'data file'
group by ServerName,DBName,AsOfDate;

ScottPletcher
Constraint Violating Yak Guru

USA
372 Posts

Posted - 08/20/2013 :  17:35:51  Show Profile  Reply with Quote

select ServerName, DBName, AsOfDate, 
    case when row_num_min = 1 then FileUsedMB end AS MinimumSize,
    case when row_num_max = 1 then FileUsedMB end AS MaximumSize    
from (
    select ServerName, DBName, AsOfDate, FileUsedMB
     , ROW_NUMBER() OVER(PARTITION BY ServerName, DBName ORDER BY FileUsedMB) AS row_num_min
     , ROW_NUMBER() OVER(PARTITION BY ServerName, DBName ORDER BY FileUsedMB DESC) AS row_num_max
     from DBSize
     where FileType = 'data file'
) as derived
where row_num_min = 1 or row_num_max = 1

Edited by - ScottPletcher on 08/20/2013 17:36:15
Go to Top of Page

jbates99
Constraint Violating Yak Guru

379 Posts

Posted - 08/20/2013 :  20:43:27  Show Profile  Reply with Quote
Thanks, ScottPletcher. For me, that's very advanced TSQL.

Is it possible to return only 1 row per database - containing both min and max sizes?

Jack
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 08/20/2013 :  21:07:36  Show Profile  Reply with Quote
quote:
Originally posted by jbates99

Thanks, ScottPletcher. For me, that's very advanced TSQL.

Is it possible to return only 1 row per database - containing both min and max sizes?

Jack

Scott's query will return one row per database. The key is the PARTITION BY clause he is using "PARTITION BY ServerName, DBName".

Even though the query may look complex, you can break it up into parts and understand it. Run the inner query by itself and you will see that it is simply adding a ranking number to each row. The outer query then uses that ranking and picks only those that have rank = 1 (for min and max).

What I meant by "inner query" is this:
select ServerName, DBName, AsOfDate, FileUsedMB
     , ROW_NUMBER() OVER(PARTITION BY ServerName, DBName ORDER BY FileUsedMB) AS row_num_min
     , ROW_NUMBER() OVER(PARTITION BY ServerName, DBName ORDER BY FileUsedMB DESC) AS row_num_max
     from DBSize
     where FileType = 'data file'


Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
328 Posts

Posted - 08/21/2013 :  11:02:27  Show Profile  Reply with Quote
James, there is no aggregation here. Given his sample data with some extrapolation:

Server1 master 1-1-2013 25 MB
Server1 master 1-2-2013 26 MB
..
Server1 master 7-11-2013 27 MB
Server1 master 7-12-2013 28 MB

Both the first and last entry will get Row_num of 1 for Min & Max respectively. Since the outer query doesn't aggregate, the resultset will contain 2 rows for each db.

I may chose to do this a bit differently given more time, but this should fix it for you jbates:

select ServerName, DBName, 
    MAX(case when row_num_min = 1 then AsOfDate end) AS MinimumSizeDate,
    MAX(case when row_num_min = 1 then FileUsedMB end) AS MinimumSize,
    MAX(case when row_num_max = 1 then AsOfDate end) AS MaximumSizeDate,
    MAX(case when row_num_max = 1 then FileUsedMB end) AS MaximumSize    
from (
    select ServerName, DBName, AsOfDate, FileUsedMB
     , ROW_NUMBER() OVER(PARTITION BY ServerName, DBName ORDER BY FileUsedMB) AS row_num_min
     , ROW_NUMBER() OVER(PARTITION BY ServerName, DBName ORDER BY FileUsedMB DESC) AS row_num_max
     from DBSize
     where FileType = 'data file'
) as derived
where row_num_min = 1 or row_num_max = 1
GROUP BY ServerName, DBName


* EDIT: Rushed the solution and forgot the date would be different and it needs to be part of the aggregate / piviot

Edited by - lazerath on 08/21/2013 11:17:07
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/21/2013 :  11:27:42  Show Profile  Reply with Quote
Here is another possible solution, although if you have the same min size on the same day you might get duplicates, so you can add a distint or a group by if that is the case:
SELECT
	DBSize.Servername,
	DBSize.DBName,
	DBSize.AsOfDate,
	T.MinimumSize,
	T.MaximumSize
FROM 
	DBSize 
INNER JOIN
	(
		SELECT ServerName, DBName, MIN(FileUsedMB) AS MinimumSize, MAX(FileUsedMB) AS MaximumSize
		FROM DBSize
		WHERE FileType = 'data file'
		GROUP BY ServerName, DBName
	) AS T
	ON DBSize.ServerName = T.ServerName
	AND DBSize.DBName = T.DBName
	AND DBSize.FileUsedMB = T.MinimumSize
	AND DBSize.FileType = 'data file' -- Not sure if this is needed or not
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
328 Posts

Posted - 08/21/2013 :  14:00:15  Show Profile  Reply with Quote
Lamprey,

Two problems:
1.) As you said, this method would produce inconsistent results unless you had a unique constraint on the FileUsedMB column. I would advise against it for production business usage.
2.) You are only displaying the date of the Minimum Size entry. Granted, the user provided an example with only the Minimum Size date, however it appears in his sample that he wants the date information along with both the Min & Max.
Go to Top of Page

jbates99
Constraint Violating Yak Guru

379 Posts

Posted - 08/21/2013 :  22:01:06  Show Profile  Reply with Quote
Thank you very much, Lamprey and Lazerath!

Lazerath, I only had to add 1 line:
ORDER BY ServerName, DBName -- to get all of each servers databases to appear together

.. And you are correct - I do want BOTH dates to be returned.

This gives exactly what I needed:

ServerName DBName MinimumSizeDate MinimumSize MaximumSizeDate MaximumSize
DCSQL001 AHT_Prod 2013-01-14 09:36:00 113000 2013-08-21 07:00:00 143262
DCSQL001 DOVE 2013-01-14 13:02:00 823 2013-08-21 07:00:00 994
DCVSRV151 tkcsdb 2013-01-15 07:05:00 33378 2013-05-03 07:05:00 43365
Go to Top of Page

sivadss2007
Starting Member

India
18 Posts

Posted - 08/28/2013 :  06:39:36  Show Profile  Reply with Quote
select ServerName, DBName, AsOfDate
, MIN(FileUsedMB) as MinimumSize
, MAX(FileUsedMB) as MaximumSize
from DBSize
where FileType = 'data file'
group by ServerName,DBName,AsOfDate
order by asofdate;

P.Siva
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000