Min & max value in each row - SQL Server Forums

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

Transact-SQL (2005)

Min & max value in each row

Author

Topic

Mountain_Nerd
Starting Member

28 Posts

Posted - 2007-01-02 : 12:45:57

I have an interesting problem that I already solved for my purposes, but I don't like the solution. I'm sure someone else has a much better idea.

I have a table containing address ranges for the left and right sides of the street. I need to know the min and max values of the from-address and to-address in each row (NOTE: the from-address is not necessarily less than the to-address). This is my ugly, but fast solution:


select *
from 
(
	SELECT 
		CASE
			WHEN x.minF > x.minT THEN x.minT
			ELSE x.minF
		END AS minADD,
		CASE
			WHEN x.maxF > x.maxT THEN x.maxF
			ELSE x.maxT
		END AS maxADD
	FROM
	(
	SELECT
		CASE
			WHEN L_F_ADD < R_F_ADD THEN L_F_ADD
			WHEN L_F_ADD > R_F_ADD THEN R_F_ADD
		END AS minF,
		CASE
			WHEN L_F_ADD > R_F_ADD THEN L_F_ADD
			WHEN L_F_ADD < R_F_ADD THEN R_F_ADD
		END AS maxF,
		CASE
			WHEN L_T_ADD < R_T_ADD THEN L_T_ADD
			WHEN L_T_ADD > R_T_ADD THEN R_T_ADD
		END AS minT,
		CASE
			WHEN L_T_ADD > R_T_ADD THEN L_T_ADD
			WHEN L_T_ADD < R_T_ADD THEN R_T_ADD
		END AS maxT
	FROM myBlocks
	) x
)

This works well as long as there are only four columns. The other idea I had was to throw each row's values into a temp table and use the MIN() and MAX() functions, but I figured that performance would be very poor. So... how would you approach this problem if you had 12 columns... or X columns?

Thanks,
Chris

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 14:56:16

Something like this

SELECT    d.PrimaryKeyCol,
          MIN(d.Col),
          MAX(d.Col)
FROM      (
               SELECT PrimaryKeyCol, Col1 AS Col FROM <YourTableNameHere> UNION ALL
               SELECT PrimaryKeyCol, Col2 FROM <YourTableNameHere> UNION ALL
               ...
               SELECT PrimaryKeyCol, ColX FROM <YourTableNameHere>
           ) AS d
GROUP BY  d.PrimaryKeyCol
ORDER BY  d.PrimaryKeyCol

Please post some sample data for more accurate suggestion for solution.

Peter Larsson
Helsingborg, Sweden

Mountain_Nerd
Starting Member

28 Posts

Posted - 2007-01-02 : 15:44:57

I knew there had to be a better way, but I just wasn't seeing it! Simple, elegant, and fast. Thanks for your solution, Peter.

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2007-01-02 : 22:07:02

hi

This is interesting. I cannot visualize how is it going to look like in the query result. is it possible to post some fictitious data? thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 23:03:56

[code]-- prepare sample data
declare @t table (primarykeycol int, col1 int, col2 int, col3 int, col4 int, col5 int, col6 int)

insert @t
select 1, 99, 6, -2, null, 150, 22 union all
select 2, 10, 11, 200, 32, 15, 20

-- do the work
SELECT d.PrimaryKeyCol,
MIN(d.Col),
MAX(d.Col)
FROM (
SELECT PrimaryKeyCol, Col1 AS Col FROM @t UNION ALL
SELECT PrimaryKeyCol, Col2 FROM @t UNION ALL
SELECT PrimaryKeyCol, Col3 FROM @t UNION ALL
SELECT PrimaryKeyCol, Col4 FROM @t UNION ALL
SELECT PrimaryKeyCol, Col5 FROM @t UNION ALL
SELECT PrimaryKeyCol, Col6 FROM @t
) AS d
GROUP BY d.PrimaryKeyCol
ORDER BY d.PrimaryKeyCol[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources