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

This works for me in MySQL 5.1:

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

Addendum

@rexem pointed out that the original answer will likely not work on SQL Server.

I've gotten this other method to work, but it's pretty hackish. In theory, if you order by Number, then the first row should be your minimum and just limit your results to one row:

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

As I said, seems sloppy, but gets the job done for me. My apologies for not being able to test these on SQL Server instead of MySQL.

Answer 2

No, you can't do this without a subquery.

Answer 3

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.

Answer 4

In MSSQL you can do

select top 1 ID, Number from TableA order by Number

(similar to Brent Nash's answer above)

Answer 5

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 6

Test I did with the Having cause:

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

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

so it doesn't work.

Yes, I agree this "select top 1 ID, Number from TableA order by Number" works, but it gets ugly if it's a complex query.

It looks like I am happy with what the original query I had for now

Answer 7

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