Simplified SQL Min Statement

Question

The SQL below returns any records with min number and it should work fine:

SELECT Id, Number 
  FROM TableA
 WHERE Number = (select Min(Number) from TableA)

Is there way I can write a SQL without the sub Select statement, but still returns the same result?

Answer 1

The OP's query will return multiple rows in the case of ties for min(Number) Some of the answers given so far will only return one row.

To use TOP and ORDER BY, WITH TIES needs to be included:

 select top 1 with ties id, number
 from TableA 
 order by Number

And if using the CTE or inline-view with a windowed function, RANK() instead of ROW_NUMBER() needs to be used:

 ; with CTE (R, ID, Number) as 
    (select rank() over (order by Number)
        , ID, Number
     from TableA)
select ID, Number
from CTE
where R = 1

Also, please benchmark before replacing your query with one of the above. For a very small table that I used to test with, the query in the OP costed out at less than half either the TOP WITH TIES or the RANK() versions listed in this answer.

Answer 2

with table_ranked(d, numb, rk) as (
    select
       id,
       number,
       row_number() over (order by number asc) as rk
    from TableA
)
 select
  d,
  numb
 from table_ranked
 where rk = 1

The above could be an idea if you have access to a database that implements the with clause and row_number like an oracle 10g environment.

you could obviously also do the following:

 select
  id,
  number
 from (select
         id,
         number,
         row_number() over (order by number asc) as rk
       from TableA
       ) table_ranked
 where rk = 1

Neither of these really answers your original question but could be considered as alternate strategies.