Simplified SQL Min Statement

Question

The SQL below returns any records with a minimum number, and it should work fine:

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

Is there a way to write SQL without a sub Select statement, but still return the same result?

Answer 1

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 idea may be an idea if you have access to a database that implements a proposal withand row_number, like an oracle 10g environment.

you could 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

None of them answer your initial question, but can be considered as alternative strategies.

Answer 2

OP min (Number). , , .

TOP ORDER BY, WITH TIES:

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

CTE , RANK() ROW_NUMBER():

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

, , . , , OP TOP WITH TIES RANK(), .

Answer 3

MySQL 5.1:

SELECT Id,Number FROM TableA HAVING Number=MIN(Number);

@rexem , , , SQL Server.

, . , , :

SELECT Id,Number FROM Table A ORDER BY Number LIMIT 1

, , . SQL Server MySQL.

Answer 4

MSSQL

select top 1 ID, Number from TableA order by Number

( )

Answer 5

, :

SELECT Id, Number 
FROM TableA
Group by Id, Number 
Having  Number = Min(Number) 

:

ID  Number
1   1
4   1
5   2
6   2
2   3
3   3
7   6

.

, , " top 1 ID, Number from TableA order by Number" , , .

, ,

Answer 6

SELECT a.Id, a.Number FROM TableA a 
inner join (select Min(Number) from TableA) m 
on a.Number = m.Number

Answer 7

, .