SQL script to find winners on auction site

Question

I am developing an auction site in PHP and Mysql and I want to display the report of the winners - that is, of the people who made the biggest bids in different auctions (posts).

This is the structure of the entity that stores the bids is comentario(id_com, id_user, id_post, prospota). I would like to return all who have made the highest bid of each auction.

insert image description here

Answer 1

Assuming that no bids can be repeated for each auction, it is possible to do the following query

SELECT c1.*
FROM comentario c1
INNER JOIN (SELECT ci.id_user, MAX(ci.proposta) AS maiorproposta
            FROM comentario ci GROUP BY ci.id_post) c2
  ON (c1.proposta = c2.maiorproposta);

For this, I assumed that Mysql 5 is used with the following structure

CREATE TABLE IF NOT EXISTS `comentario` (
  `id_com` int(6) unsigned NOT NULL,
  `id_user` int(6) unsigned NOT NULL,
  `id_post` int(6) unsigned NOT NULL,
  `proposta` float NOT NULL,
  PRIMARY KEY (`id_com`)
) DEFAULT CHARSET=utf8;

I provide a SQL Fiddle with the answer in this link.