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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 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)

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.

Gianmaria Gregori · Answer 3 · 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

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

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0