Group by pick row with lowest value (price)

Question

I have all the hotel and room price, in these data, I need to fetch the lowest price room among the hotels data, below is my query it is not picking the least price example hotelname3, room2, 5.

SELECT *
FROM (hoteldata)
GROUP BY hotelname
ORDER BY price ASC

Possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Strawberry, Dec 12 '15 at 07:16

am90 · Answer 1 · 2015-12-12T08:03:12.593

0

Try this:

SELECT * FROM hoteldata where price = (SELECT min(price) FROM hoteldata)

edited Dec 12 '15 at 08:03

answered Dec 12 '15 at 07:57

am90

201
2
11

score 0 · Answer 2 · answered Dec 12 '15 at 14:24

SELECT hd.*
FROM (
    SELECT hotelname , MIN(price) as minprice
    FROM hoteldata
    GROUP BY hotelname
) minprices
JOIN hoteldata hd 
    ON  hd.hotelname = minprices.hotelname
    AND hd.price = minprices.minprice

Note: If two rooms in one hotel have the lowest price, they will be listet both.

For performance you should define an index on (hotelname, price)

http://sqlfiddle.com/#!9/10f00/1

score -1 · Answer 3 · edited Dec 12 '15 at 08:47

-1

SELECT *
FROM (hoteldata) order by price asc limit 1;

This will work for getting minimum price room from different hotel.

edited Dec 12 '15 at 08:47

Nisse Engström

4,738
23
27
42

answered Dec 12 '15 at 08:00

Man-Ty's

1
1

Group by pick row with lowest value (price)

3 Answers3