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)
精彩评论