开发者

Difference in columns. Returning variable top duplicate results

开发者 https://www.devze.com 2023-03-10 18:13 出处:网络
I have a query that returns the biggest difference in two columns. Lets say it\'s something like: result |

I have a query that returns the biggest difference in two columns. Lets say it's something like:

| result |
    5
    5
    5
    4
    4
    3
    2

How can I make it return all of the top results every time? (5, 5, 5)

I'm not looking for LIMIT 3, since the results vary and some开发者_如何学Ctimes there's only one top number, etc.

The query I have now looks like this:

SELECT MAX(column1 - column2) as result
FROM table
WHERE othercolumn = somecondition
GROUP by id
ORDER BY results asc


SELECT MAX(column1 - column2) as result
FROM table
WHERE othercolumn = somecondition
GROUP by id
HAVING result = 
  (select max(column1 - column2) FROM table WHERE othercolumn = somecondition)
ORDER BY id;


Not tested but it might work

select *,column1-column2 as result from table where column1 - column2 = 
(SELECT (column1 - column2) as G
FROM table
ORDER BY G DESC limit 1)
0

精彩评论

暂无评论...
验证码 换一张
取 消