SQL GROUP BY AND VALUE

I have a table

AvailbilityDate |   Resort    |  AccomName  |  Price    |  Min Occupancy
24 June 2012    |    Resort1  |    Accom1   |     999   |    8
24 June 2012    |    Resort1  |    Accom2   |     888   |    6
24 June 2012    |    Resort2  |    Accom1a  |     243   |    10
24 June 2012    |    Resort2  |    Accom2a  |     563   |    7

What I currently have is

SELECT AvailbilityDate, Resort, MIN(Price) AS Lowest 
FROM mytable 
GROUP BY AvailbilityDate, Resort

I want to be able to get the A开发者_高级运维ccomName and the Min Occupancy

Many thanks in advance

---

With standard ANSI SQL, the solution would be this:

SELECT *
FROM (
   SELECT AvailbilityDate, 
          resort,
          accomName,
          price,
          min_occupancy,
          min(price) over (partition by AvailbilityDate, Resort) as min_price
   FROM deals_panel_view
) t
WHERE min_price = price;

Should work on PostgreSQL, Oracle, DB2, SQL Server, Sybase and Terradata

---

Using a common table expression and Ranking functions you can do this

WITH cte as (
SELECT 
  ROW_NUMBER() over (PARTITION BY AvailabilityDate,Resort ORDER BY price) as row,
  AvailbilityDate,
  Resort,
  AccomName,
  Price,
  [Min Occupancy] 
FROM mytable  
)
SELECT AvailbilityDate,Resort,Price,AccomName,[Min Occupancy] from cte where row=1

---

SELECT AvailbilityDate, Resort, AccomName, "Min Occupancy", MIN(Price) AS Lowest 
FROM mytable 
GROUP BY AvailbilityDate, Resort

---

You can do this in several ways.

1) Ranking - per a_horse_with_no_name's solution

2) Group by and Cross Apply. Essentially plug the columns you want to group up in the first subquery and then all other columns go into the second subquery. Use an Order By in the second subquery to deal with any column you want to apply MIN (or MAX) to.

SELECT a.AvailbilityDate, 
       a.resort,
       b.AccomName,
       b.min_occupancy,
       b.Lowest
FROM 
(
 SELECT t1.AvailbilityDate, t1.resort
  FROM myTable t1
  GROUP BY t1.AvailbilityDate, t1.resort
 ) a
CROSS APPLY
( 
  SELECT TOP 1 t2.AccomName, t2.min_occupancy, t2.price as Lowest
      FROM mytable t2
         WHERE t2.AvailbilityDate = a.AvailbilityDate
          AND t2.resort = a.resort
          ORDER BY t2.price ASC
) b

3) Use subqueries in select statement (not very elegant but works) with Group By. This is assuming that there is only one accomName with the given minimum price for each combination of AvailbilityDate and resort.

SELECT a.AvailbilityDate, 
       a.resort,
       (SELECT accomName FROM myTable t1
         WHERE t1.AvailbilityDate = a.AvailbilityDate
          AND t1.resort = a.resort
          AND t1.price = MIN(a.price)
       ) as accomName,
       (SELECT min_occupancy FROM myTable t1
         WHERE t1.AvailbilityDate = a.AvailbilityDate
          AND t1.resort = a.resort
          AND t1.price = MIN(a.price)
       ) as min_occupancy,
       MIN(a.price) as Lowest
  FROM myTable a
  GROUP BY a.AvailbilityDate, a.resort

---

SELECT AccomName, MIN([Min Occupancy]) AS Lowest FROM mytable GROUP BY AccomName

That would give you ONLY the desired Infos. If you want to have the desired fields in ADDITION to what you have already, it would be like:

SELECT AvailbilityDate, AccomName, Resort, MIN(Price) AS Lowest, Min([Min Occupancy]) As LowestMinOcc FROM mytable GROUP BY AvailbilityDate , AccomName , Resort