Question: I am practicing SQL queries using a hotel booking database in MYSQL.
There are 4 tables which are Guest, room, hotel and booking in the database.
I want to write a SQL query to retrieve details of hotel rooms which are having the lowest price. There are more than one record with the minimum price and the task is to retrieve all of them and not only one record.

```
SELECT * FROM room ORDER BY Price LIMIT 1;

```

This will give me only one record and I know there are more than one record yet I don't know exactly how many records are there with the minimum price so that I guess I can't use ORDER BY and LIMIT to perform this task.

Following is a part of the table that I want to query.
Table Name: Room.

[![Table Name Room:](https://i.stack.imgur.com/J9won.png)](https://i.stack.imgur.com/J9won.png)

In this room table, Hotel\_No references Hotel\_No in the hotel table and the PRIMARY KEY of this table is both Hotel\_No and Room\_No together.

How do I do this in MYSQL?

Answer: 
If you want all records that have the minimum price over the whole table, one cross-database solution is to filter with a correlated subquery:

```
select r.*
from room r
where r.price = (select min(r1.price) from room r1)

```

Depending on your database, better solutions might be available. Typically, SQL Server has `TOP 1 WITH TIES`:

```
select top 1 with ties r.* from room r order by r.price

```

Or, if your database support window functions, you can use `rank()`:

```
select *
from (select r.*, rank() over(order by price) rn from room r) t
where rn = 1

```
```
SELECT t.*
FROM Room t
LEFT JOIN Room t1 ON t1.Price < t.Price
WHERE t1.Id IS NULL

```

This assumes your table has an Id column - if it doesn't you can just use `WHERE t1.Price IS NULL` on the last line.

It returns all records for which no lower price exists.