CODEWITHSUNDEEP
sql-server
kaira
kaira

Reputation: 13

Minimum and max of two column

I want to find the minimum value present in 2 columns and max value present in 2 column EX: A :2 3 4 B: 5 6 7

Ans should be 2 for min and 7 for max. A and B are columns in same table. min() inbuilt function doesn't exists.

Upvotes: 0

Views: 783

Answers (5)

Cade Roux
Cade Roux

Reputation: 89661

If there is indexing which is appropriate, it might be more efficient to do the MIN/MAX independently first on columns A and B since it would hopefully use some seeks into appropriate indexes on those columns:

SELECT MIN(mn), MAX(mx)
FROM (
    SELECT MIN(A) AS mn, MAX(A) AS mx FROM tbl
    UNION ALL
    SELECT MIN(B) AS mn, MAX(B) AS mx FROM tbl
) x;

Upvotes: 0

JosephStyons
JosephStyons

Reputation: 58685

Self-contained example.

declare @t table(a int, b int)
insert into @t values(2,5)
insert into @t values(3,6)
insert into @t values(4,7)

select min(a) as a,max(b) as b from @t

--if for some reason min() and max() are 'not available' (???)
select
  (select top 1 a from @t order by a asc) as a
 ,(select top 1 b from @t order by a desc) as b

Upvotes: 0

user170442
user170442

Reputation:

You could go with:

SELECT MIN(val), MAX(val) FROM (
  SELECT a val FROM Table
  UNION
  SELECT b FROM Table
) d

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269643

I think the simplest way is apply:

select min(x.val), max(x.val)
from t cross apply
     (select val
      from (values (t.col1), (t.col2)) v(val)
     ) x;

The cross apply simply unpivots the values into a single column, before running the aggregation functions.

Upvotes: 3

vinay koul
vinay koul

Reputation: 346

you can use Max and Min function to determine the max n min values for that column.

  Select Min(A),Max(B) from Table

Upvotes: -1

Related Questions