SQL - selecting all rows with maximum value

Question

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?

And which is the maximum price of the table? At least should be just 1 max price, so you will have 1 row — albanx, Jul 08 '12 at 08:20
Also `GROUP BY id ` is not needed, id should be unique and the `JOIN` does it for you. — albanx, Jul 08 '12 at 08:23
@Dan Yes, the price is equals to the number of occurences of that ID. It's not the same for all IDs. — ryyst, Jul 08 '12 at 08:28
possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — akaihola, Oct 08 '12 at 06:39

score 3 · Answer 1 · edited Jul 26 '13 at 18:48

3

This looks fairly simple to me:

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

edited Jul 26 '13 at 18:48

ckpepper02

3,297
5
29
43

answered May 02 '13 at 13:18

user970780

61
7

Zane Bien · Answer 2 · 2012-07-08T19:10:03.967

1

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.

edited Jul 08 '12 at 19:10

answered Jul 08 '12 at 08:37

Zane Bien

22,685
6
45
57

It's a matter of preference I suppose, but I used your version as a template. I'm voting for you. – shawnt00 Jul 08 '12 at 09:03
I was hoping to avoid having to write parts of the query redundantly. Is there any way around it? – ryyst Jul 08 '12 at 11:04
@ryyst, I'm afraid there is no way around it. This is the most efficient solution I know of that actually works in MySQL. The important thing to note is that I'm using strictly joins for subselects which execute only once as opposed to `WHERE` or `HAVING` subqueries that execute for each row. @shawnt00's solution would be nice if MySQL supported CTE's, but it doesn't. – Zane Bien Jul 08 '12 at 17:43

Nikson Kanti Paul · Answer 3 · 2012-07-08T10:46:12.960

0

HAVING is used to check conditions after the aggregation takes place.

WHERE is used before the aggregation takes place.

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

edited Jul 08 '12 at 10:46

answered Jul 08 '12 at 08:17

Nikson Kanti Paul

3,394
1
35
51

2

`WHERE price = MAX(price)` never works. `max()` is a single-value function that can not directly be used in the `WHERE` clause of SQL. – Lion Jul 08 '12 at 08:19
It's used in a subquery. That's perfectly fine. – shawnt00 Jul 08 '12 at 08:32
The problem is that price is only defined in the outermost `SELECT` statement, so when writing `SELECT MAX(price) FROM ...table`, I would need a name for that table. – ryyst Jul 08 '12 at 08:47
@ryyst, I was referring to the concept of using a subquery. Your arrangement here is a bit unusual. Could you elaborate on the reason that price is defined by counting rows? – shawnt00 Jul 08 '12 at 08:52
Well, imagine a table where items are associated with individual coins. So if an item is associated with 10 coins, it has the price 10. The situation is a bit more complex, but that's the general idea. – ryyst Jul 08 '12 at 08:57
These queries are little to be inefficient if you're not careful. Creating a name for the value is easy to accomplish by wrapping the query with another SELECT. You'll also notice that my answer works around the need to refer to max price by name. – shawnt00 Jul 08 '12 at 09:43

albanx · Answer 4 · 2012-07-08T08:26:56.493

0

try this, put MAX in select, this should be the correct way

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;

edited Jul 08 '12 at 08:26

answered Jul 08 '12 at 08:21

albanx

6,193
9
67
97

It says "unknown column 'price' in field list". – ryyst Jul 08 '12 at 08:24
This query `SELECT * FROM rt WHERE somecondition` should return a table name. verify that this table has the column price? – albanx Jul 08 '12 at 08:25
@ryyst:) Try using `MAX(st.price)`. – Lion Jul 08 '12 at 08:27
And it should be `SELECT some_table_name FROM rt WHERE somecondition LIMIT 1` – albanx Jul 08 '12 at 08:27

score 0 · Answer 5 · answered Jul 08 '12 at 08:49

Assuming that @Zane's answer is what you do want, here's a portable version of his query that also avoids LIMIT/TOP operations. I'm not really familiar with mysql dialects, but I imagine this will work without problem.

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 [somecondition]
    GROUP BY aa.id
) a
WHERE
    a.price >= ALL (
        SELECT COUNT(1) AS maxprice
        FROM rt aa
        INNER JOIN tt bb ON aa.id = bb.id
        WHERE [somecondition]
        GROUP BY aa.id
    )

score 0 · Answer 6 · answered Jul 08 '12 at 17:26

You asked for an approach that didn't require the redundancy of stating the inner query more than once. That's certainly what a cte is good for. These are two other solutions rewritten to use that tactic.

WITH basequery as (
    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
)
SELECT a.id, a.price
FROM
    basequery as a INNER JOIN
    (SELECT MAX(price) AS maxprice FROM basequery) as b
        ON a.price = b.maxprice
-- or
WITH basequery as (
    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
)
SELECT a.id, a.price
FROM
    basequery as a
WHERE
    a.price >= ALL (SELECT price FROM basequery)

Interesting, I'm learning more about CTE's everyday. This would be awesome if only MySQL supported it. =( — Zane Bien, Jul 08 '12 at 17:48

SQL - selecting all rows with maximum value

6 Answers6

Linked