CODEWITHSUNDEEP
sqlmin
user2368457
user2368457

Reputation: 75

SQL selecting minimum value in a sub query when exact value is unknown

I have an SQL query that is meant to select a list of things from different tables using a subquery. I am meant to find those things with the lowest value in a particular column.

This is the query that i currently have. I know the minimum rate is 350 but i cant use it in my query. Any effort to change it to MIN(rate) has been unsuccessful.

  SELECT DISTINCT name
  FROM table1 NATURAL JOIN table2
  WHERE table2.code = (SELECT Code FROM rates WHERE rate = '100')

How do i change that subquery to find the minimum rate?

Upvotes: 2

Views: 25369

Answers (3)

roman
roman

Reputation: 117345

Most general way to do this would be

select distinct name
from table1 natural join table2
where
    table2.code in
    (
        select t.Code
        from rates as t
        where t.rate in (select min(r.rate) from rates as r)
    )

if you have windowed functions, you can use rank() function:

...
where
    table2.code in
    (
        select t.Code
        from (
            select r.Code, rank() over(order by r.rate) as rn
            from rates as r
        ) as t
        where t.rn = 1
    )

in SQL Server you can use top ... with ties syntax:

...
where
    table2.code in
    (
        select top 1 with ties r.Code
        from rates as r
        order by r.rate
    )

Upvotes: 3

Ashutosh Arya
Ashutosh Arya

Reputation: 1168

SELECT DISTINCT name
FROM table1 NATURAL JOIN table2
WHERE table2.code = 
(SELECT CODE FROM RATE WHERE RATE=(SELECT MIN(RATE) FROM RATE))

Considering you are expecting only one record of minimum value.

Upvotes: 1

John Woo
John Woo

Reputation: 263703

try this,

WHERE table2.code = (SELECT Code FROM rates ORDER BY rate LIMIT 1)

Upvotes: 0

Related Questions