Select the row with some minimum value while get 'more than 1 value'error?

Question

I would like to do something like this:

    select * from challenger 
        where sqrt( square(Launch_temperature-70) )=
   ( select min( sqrt( square(Launch_temperature-70) ) ) 
        from challenger)

But if I use other two ways below, SQL Server 2017 gives me this kind of error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

with cte(value) 
as
(
    select sqrt(square(Launch_temperature- 70)) as value 
    from challenger
)
select  * from challenger 
order by 
(select value from cte) ASC;

or

select  * from challenger 
    order by 
(select sqrt(square(Launch_temperature-70)) from challenger) ASC;

Why is that? Anyway to fix to this?

Edit: I have found a solution! One way:

select top 1 * from challenger 
order by 
abs(Launch_temperature-70) ASC

The other way:

with cte(value) 
as
(
    select sqrt(square(Launch_temperature- 70)) as value 
    from challenger
)

select  top 1 * from challenger, cte 
order by 
(value) ASC;

The error is literally telling you the problem here. Your subquery returned more than one row. There is no aggregate, `TOP`, or join predicate so it'll return as many rows as there are in the table `challenger`. If you run the query `SELECT SQRT(SQUARE(Launch_temperature-70)) FROM dbo.challenger;` you'll see this. What's wrong with your first query? It would work (probably not the most efficient, but it'll run). — Thom A, Dec 08 '18 at 23:20
@Larnu I am just new to SQL. I thought by "order by" is a place to put some column name or some values with #values==#original table to "order by". First piece of code works, but if any way to modify the second and third to make them work? — JP Zhang, Dec 08 '18 at 23:27
`sqrt(square(n)) = n` ? So why the square root of a square? Isn't the query you look for then simply this: `select top 1 * from challenger order by Launch_temperature asc` ? — LukStorms, Dec 08 '18 at 23:36
Thank you! I didn't think of this. But actually it should be abs(Lauch_temperature). @LukStorms — JP Zhang, Dec 09 '18 at 00:55
@HABO About that "other way" solution you added to your question. Beware that it has a cartesion join [that one normally tries to avoid](https://stackoverflow.com/a/53583444/4003419). — LukStorms, Dec 09 '18 at 12:11

Paul Maxwell · Answer 1 · 2018-12-09T01:05:16.557

If you perform the calculation in a cte, then reuse that cte

WITH cte
AS (
    SELECT
          *
        , SQRT( SQUARE( Launch_temperature - 70 ) ) AS value
    FROM challenger
)
SELECT
    *
FROM cte
WHERE value = (SELECT MIN( value ) FROM cte)
ORDER BY
    value

As you now see ABS(Launch_temperature) as the requirement, another way to do this is:

SELECT
    *
FROM (
    SELECT
        *
      , ROW_NUMBER() OVER (ORDER BY ABS( Launch_temperature )) AS rn
    FROM challenger
) AS d
WHERE rn = 1

Select the row with some minimum value while get 'more than 1 value'error?

1 Answers1