i want to return the MIN and MAX values from the given table using SQLSERVER 2008

Question

0.00/5 (No votes)

See more:

SQL

create table numb_tab(col_1 int, col_2 int,col_3 int)

insert into numb_tab values(12,45,23),(23,87,37),(2,34,13)

my question is how to return MIN and MAX (2 and 87) values from above table (numb_tab)
and how to write the query and how many methods are there ?

can you please give me answer................?

Posted 20-May-13 7:49am

Madhu from Bangalore

Add a Solution

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gianmaria Gregori · Answer 1 · 2013-05-20T08:05:00

Solution 1

I don't know if there are best ways, but this works

SQL

SELECT MIN(col) AS Min, MAX(col) AS Max FROM (
    SELECT col_1 AS col FROM numb_tab 
    UNION SELECT col_2 FROM numb_tab
    UNION SELECT col_3 FROM numb_tab) t

Posted 20-May-13 8:05am

Gianmaria Gregori

Comments

Maciej Los 20-May-13 16:12pm

+5

OriginalGriff · Answer 2 · 2013-05-20T08:08:00

You can't specifically do that - you can't say MAX(List of columns), so it gets more complex. There are a couple of solutions here: http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns[^] - they are date related, but its the same thing with integers.
Obviously you would need to duplicate it for max and min.

Maciej Los · Answer 3 · 2013-05-20T10:16:00

Solution 1 is very good. You can do it the same with CTE[^]:

SQL

;WITH MinMaxVal
AS
(
	SELECT col_1 As MyVal
	FROM @numb_tab
	UNION ALL
	SELECT col_2 As MyVal
	FROM @numb_tab
	UNION ALL
	SELECT col_3 As MyVal
	FROM @numb_tab
)
SELECT MIN(MyVal) AS MinVal, MAX(MyVal) AS MaxVal
FROM MinMaxVal
--uncomment below line if execution failed ;)
--OPTION (MAXRECURSION 0)

i want to return the MIN and MAX values from the given table using SQLSERVER 2008

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

i want to return the MIN and MAX values from the given table using SQLSERVER 2008

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us