Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Function to get Max, Min values from given collection of values Expand / Collapse
Author
Message
Posted Wednesday, January 19, 2011 8:43 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 14, 2013 3:48 PM
Points: 444, Visits: 162
Comments posted to this topic are about the item SQL Function to get Max, Min values from given collection of values
Post #1050474
Posted Thursday, January 20, 2011 8:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:34 AM
Points: 1,174, Visits: 2,654
An alternative (simply a refactor):


DECLARE @v VARCHAR(1000)
SET @v = '2,22,5,1,88,2000,7897,800'

SELECT CAST( x.query('fn:min(number)') AS varchar),
CAST( x.query('fn:max(number)') AS varchar),
CAST( x.query('fn:count(number)') AS varchar)
FROM
(
SELECT CAST('<number>' + REPLACE(@v,',','</number><number>') + '</number>' AS XML) AS X
) s


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #1050855
Posted Saturday, January 22, 2011 12:33 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 1,945, Visits: 3,017
These are called GREATEST() and LEAST() in Oracle. They are more easily done with along parameter list and no proprietary code.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1051988
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse