How to get min values for each field? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to get min values for each field?

Author

Topic

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-06-27 : 17:26:07

Hi there, can anyone help me make a stored procedure that accepts a table name as its parameter and returns field names and their respective minimum values? I searched around and found a query to get all the field names:

SELECT [Field] = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableName'

I've tried a dozen different ways to alter that to get the MIN values but none have worked.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-27 : 17:32:09

SELECT 'select min(' + COLUMN_NAME + ') + ' from ' + TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableName'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-06-27 : 17:52:28

Thanks tkizer, but I'm getting an error regarding a missing single quote. I tried putting it after TABLE_NAME but it still didn't work.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-27 : 18:12:58

Sorry:

SELECT 'select min(' + COLUMN_NAME + ')' + ' from ' + TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableName'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-06-27 : 19:12:54

Thanks for clearing that up. So I tweeked it a little got it to look like this:

select min(field1) from tableName
union all select min(field2) from tableName
union all select min(field3) from tableName
etc.

Now I'm stuck on how to make the stored procedure combine the rows into a single query and execute it. Is there a way to do that?

Thanks again!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-27 : 19:21:51

Well you'd have issues with various data types if you put it into one. Isn't it just a one-time script?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-06-27 : 19:49:47

I do this frequently enough to want to write a stored procedure for it, but I'm usually checking for more than just the minimum values - I also like to check how many nulls there are, how many unique values, and some basic stats (min, max, average, etc.). Couldn't the data types all be cast as strings? The purpose of this would be to just get a quick summary of the data to see if there's anything worth investigating.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-27 : 19:59:15

Yes you could cast/convert, I'm just saying that the code will get harder.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources