mysql - SQL - 选择具有最大值的所有行

Question

我有这个 SQL 查询:

SELECT id, COUNT(*) AS price
FROM (SELECT * FROM rt WHERE somecondition) AS st
      JOIN tt
      ON st.id = tt.id
GROUP BY id;

现在，我想选择表格中具有最高价格的所有行。我已经试过了，不幸的是，它根本没有返回任何行:

SELECT id, COUNT(*) AS price
FROM (SELECT * FROM rt WHERE somecondition) AS st
      JOIN tt
      ON st.id = tt.id
GROUP BY id
HAVING price = MAX(price);

我有点迷路了，有人有什么指点吗？

Answer 1

这对我来说很简单:

select * from <table> 
where <column name> in(
   SELECT MAX(column name) FROM table
)