Limit output that has the same ID

Question

Hello! I created 2 tables and made a relation between them. In case I want to limit the amount of an item if you have the same ID, example:

Hotel IBIS - São Paulo
Hotel IBIS - Rio de Janeiro
Hotel IBIS - Porto Alegre
Hotel IBIS - Brasilia
Hotel Plaza - São Paulo
Hotel Plaza - Rio de Janeiro
Hotel Plaza - Porto Alegre
Hotel Plaza - Brasilia

The states make relation with the Hotel that is the main table. Ai on my main page is showing all hotels at once, and what I want to do is kind of show 2 of each so:

Hotel IBIS - São Paulo
Hotel IBIS - Rio de Janeiro
Hotel Plaza - São Paulo
Hotel Plaza - Rio de Janeiro

My code:

 $sql = "select * from estados join hotel on estados.id_hotel = hotel.id order by estados.id DESC limit 5";

Answer 1

I did some tests and some research, I found a solution to this case.

/* Nessa primeira parte nós definimos algumas variáveis */
SET
  @num := 0,
  @type := 'estado';

SELECT
  `estado`,
  hotel
FROM
  (
  SELECT
    hotel.hotel AS hotel,
    estado,
    @num := IF(@type = `estado`, /* Aqui nós fizemos uma comparação com o valor de estado, caso a variável "type" seja igual ao valor do campo... */
    @num + 1, /* Soma o valor da variável */
    1) AS row_number, /* Caso contrário ele define como 1 e adiciona uma alias que utilizaremos mais à frente */
    @type := `estado` AS estado_name
  FROM
    estados
  LEFT JOIN
    hotel ON(hotel.id = estados.id_hotel)
  ORDER BY
    `estado`
) AS X
WHERE X
  .row_number <= 2 /* Aqui nós utilizamos o alias para verificar e retornar quantos valores de cada grupos nós queremos. */
LIMIT 4;

My structure:

--
-- Estrutura da tabela `estados`
--

DROP TABLE IF EXISTS `estados`;
CREATE TABLE IF NOT EXISTS `estados` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_hotel` int(11) NOT NULL,
  `estado` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Estrutura da tabela `hotel`
--

DROP TABLE IF EXISTS `hotel`;
CREATE TABLE IF NOT EXISTS `hotel` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hotel` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

I used the article as a basis How to select the first/least/max Row per group in SQL, is worth reading.

Demonstration of the Code