Subconsulta with MIN AND MAX together

Asked

Viewed 95 times

1

How can I bring a column along with min() and max().... this is possible ?

select Name, Size from Production.Product Where Size in (select MIN(Size), MAX(Size) from Production.Product)

sql sql-server

2 answers

1


Here is a suggestion using a CTE with Min and Max functions, for later use in a Join:

with CTE_M as
(
  select MIN(Size) as SizeMin, MAX(Size) as SizeMax 
  from Production.Product
)

select p.Name, p.Size 
from Production.Product as p
inner join CTE_M as c
  on
    p.Size = c.SizeMin or
    p.Size = c.SizeMax

And another suggestion using a CTE with the Row_number function to sort the lines and get the lowest and highest value:

with CTE_RN as
(
  select 
    Name, 
    Size,
    row_number() over(order by Size) as RN_Asc,
    row_number() over(order by Size desc) as RN_Desc
  from Production.Product
)

select Name, Size
from CTE_RN
where RN_Asc = 1 or RN_Desc = 1

I hope it helps

0

Half an hour of study later, I was able to answer myself.. I don’t know if it’s possible to put the two together in a subquery but I used this syntax:

select Name, Size from Production.Product Where Size in (Select MAX(Size) FROM Production.Product)

Union all

select Name, Size from Production.Product Where Size in (Select MIN(Size) FROM Production.Product)

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.