CODEWITHSUNDEEP
mysqlsqloracle-database
Rahul Bhatia
Rahul Bhatia

Reputation: 1035

SQL - Min Function

My question is Write an SQL query that will determine which is the least expensive venue that will accommodate 120 people.

The Code i wrote

select v.venuename, min(v.costperday)
from venues v 
where v.venuecapacity = 120
Group By v.venuename;

I am still getting both the answers and not the Least one.

But if i remove V.venueName from the select function.....i get the correct answer!

Why is that so ?

Your help would be highly appreciated. Thanks

Upvotes: 1

Views: 373

Answers (8)

pratik garg
pratik garg

Reputation: 3342

@Rahul Bhatia

there is one more solution we can use..

select v.venuename, 
       min(v.costperday) 
 from venues v  
 where v.venuecapacity >= 120 
 Group By v.venuename
 having min(v.costperday) = (
       select min(costperday)
       from venues 
       where venuecapacity >= 120 );

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

My question is Write an SQL query that will determine which is the least expensive venue that will accommodate 120 people.

In SQL Server I would use select top 1... order by

select top 1 v.venuename, v.costperday
from venues v 
where v.venuecapacity = 120
order by v.costperday

Goggling a bit I found this syntax for MySQL and Oracle that looks like they will work but untested by me.

MySQL

select v.venuename, v.costperday
from venues v 
where v.venuecapacity = 120
order by v.costperday 
limit 1

Oracle

select v.venuename, v.costperday
from (select v.venuename, v.costperday
      from venues v 
      where v.venuecapacity = 120
      order by v.costperday
     )
where rownum = 1

Upvotes: 0

Andrew Lazarus
Andrew Lazarus

Reputation: 19330

Using CTE, can do

WITH minquery AS
 (SELECT min(costperday) AS mincost
  FROM venues
  WHERE venuecapacity>=120)
SELECT venuename, venuecapacity, costperday
  FROM venues 
  WHERE costperday=minquery.mincost AND venuecapacity>=120;

Upvotes: 0

DCookie
DCookie

Reputation: 43533

I think this might be what you want:

SELECT venuename
  FROM (SELECT venuename, costperday, MIN(costperday) OVER () mincost
          FROM venues
         WHERE venuecapacity >= 120) q
 WHERE q.costperday = q.mincost;

Use >= 120, because you might not have any venues that have exactly 120 capacity, and you want all venues that can handle at least 120. Your requirements only state that the venue be able to accommodate 120 people, not that it handle exactly 120 people.

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116110

That is because you select the lowest cost per day per venue if you include the name. In fact, the whole grouping is useless in this table as long as venuename is unique.

To get the venue with the lowest price use something like this:

select venuename, costperday
from
  venues v
where
  v.venuecapacity = 120 and
  v.costperday = (select min(costperday) from venue vs where va.venuecapacity = 120)

Upvotes: 0

Erkan Haspulat
Erkan Haspulat

Reputation: 12562

select v.venuename, min(v.costperday)
from 
(
  select venuename, costperday
  from venues
  where v.venuecapacity = 120) v

Upvotes: 0

user330315
user330315

Reputation: 

SELECT *
FROM (
    SELECT venuename, 
           costperday,
           min(costperday) over () as min_cost,
    FROM venues
    WHERE venuecapacity = 120
) v
WHERE v.min_cost = v.costperday

Or using a subselect:

SELECT *
FROM venues
WHERE venuecapacity = 120
AND costperday = (SELECT min(v2.costperday) 
                  FROM venues v2
                  WHERE v2.venuecapacity = 120)

You might want to use venuecapacity >= 120 in case there are venues that accomodate more than 120 people but are still cheaper than others that only allow exactly 120 people

Upvotes: 2

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

You include the same field in the select list, thus the min will be computed for each of them not overall items

Upvotes: 1

Related Questions