Selecting both MIN and MAX in Single SQL Statement

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 2008 Forums

Transact-SQL (2008)

Selecting both MIN and MAX in Single SQL Statement

Author

Topic

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2013-08-20 : 17:14:18

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
Aged Yak Warrior

550 Posts

Posted - 2013-08-20 : 17:35:51

[code]
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
[/code]

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2013-08-20 : 20:43:27

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-20 : 21:07:36

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'

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-21 : 11:02:27

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-21 : 11:27:42

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

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-21 : 14:00:15

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.

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2013-08-21 : 22:01:06

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

sivadss2007
Starting Member

18 Posts

Posted - 2013-08-28 : 06:39:36

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources