I have a question about SQL (I use MySQL), I don't know if it's possible but I ask you. I am currently doing a particular query, but I am not able to implement this. The image in below of this message, is a table where there are result of the top 2 result (by best column) in each country.

The query that I would like to implement this is getting difference between top2 and top1 in countryRank in each countryId. I would like to have the result like this:

Attention: some country doesn't have value 2 as countryRank like Angola in image, in this case I would like to ignore this.

Thanks for your attention!

CodePudding user response：

Split the recordset into two and use a join or use a window function. Sample here in the link.

CodePudding user response：

You can do it in a single query like this:

SELECT countryId,
       MAX(IF(countryRank=2,best,0)) - MAX(IF(countryRank=1,best,0)) AS "difference (2-1)"
 FROM mytable
GROUP BY countryId;

But on a country where there's no rank=2 (like Angola), the result will return negative -2563.

Demo fiddle