Add additional condition to MySQL query

Question

I need some help with a MySql Query. My existing query returns all rows which have 100001 as the query_id, sorted by priceperadult ASC.

However, I want it to only return the best rate per hotel, so need to add to the statement so only one rate per hotel shows, whats the best way to achieve this?

SELECT * FROM `search_results` WHERE `query_id` = '100001' ORDER BY `priceperadult` ASC

Sample Data

query_id, priceperadult, hotel_name

100001, 100, Hotel 1

100001, 200, Hotel 1

100001, 100, Hotel 2

100001, 200, Hotel 2

sgeddes · Accepted Answer · 2015-10-14 23:26:48Z

1

Sounds like you're looking to use min():

SELECT query_id, MIN(priceperadult), hotel_name
FROM search_results 
WHERE query_id = '100001' 
GROUP BY query_id, hotel_name

answered Oct 14, 2015 at 23:26

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Smedley Over a year ago

Thats great thanks for answer. But how do i then sort the list by MIN(priceperadult) in ASC fashion? i.e. lowest rate of all hotels first?

Collectives™ on Stack Overflow

Add additional condition to MySQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related