selecting all rows with maximum value

I have this SQL query:

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

Now, I want to select all rows which have the maximum price of the table. I have tried this, which unfortunately returns no row at all:

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);

I'm somewhat lost, does anybody have any pointers?

---

这对我来说看起来很简单：

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

---

Try this solution:

SELECT a.id, a.price
FROM
(
    SELECT aa.id, COUNT(1) AS price
    FROM rt aa
    INNER JOIN tt bb ON aa.id = bb.id
    WHERE aa.somecondition
    GROUP BY aa.id
) a
INNER JOIN
(
    SELECT MAX(aa.price) AS maxprice
    FROM
    (
        SELECT COUNT(1) AS price
        FROM rt aaa
        INNER JOIN tt bbb ON aaa.id = bbb.id
        WHERE aaa.somecondition
        GROUP BY aaa.id
    ) aa
) b ON a.price = b.maxprice

Edit : While I can't think of any way to rewrite this so as to not have to write the base-queries redundantly, what you could perhaps do is this:

SELECT GROUP_CONCAT(a.id) AS ids, a.price
FROM
(
    SELECT aa.id, COUNT(1) AS price
    FROM rt aa
    INNER JOIN tt bb ON aa.id = bb.id
    WHERE aa.somecondition
    GROUP BY aa.id
) a
GROUP BY a.price
ORDER BY a.price DESC
LIMIT 1

This produces a comma-separated-list of the ids that share the same maximum value. This is probably not the format you are looking for though, but it is one way to avoid having to write the base-query twice. Just putting that out there.

---

试试这个，把MAX放在select中，这应该是正确的方法

SELECT id, COUNT(*) AS price, MAX(price) AS max_price
FROM (SELECT some_table_name FROM rt WHERE somecondition LIMIT 1) AS st
      JOIN thenextTable as tt
      ON st.id = tt.id
GROUP BY id;

链接地址: http://www.djcxy.com/p/61246.html

上一篇: 如何检测JavaFX中已完成的调整大小操作？

下一篇: 选择所有具有最大值的行