CODEWITHSUNDEEP
mysqlaggregate
Roberto Adarve
Roberto Adarve

Reputation: 167

How to get the max value of a field without grouping in MySql

I have a query with many wheres and orders criterias. One of the fields of the select is 'price' (element price) but I would like to have also (in every row) the maximun price of all the selected elements.

I tried to include MAX aggregate function on select hoping that this will return desired value, but insetead of that, price and MAX(price) returns the same. Searching into MySql doc I found the reason:

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

Is there a way to solve this problem?

Thanks in advance!

There's a similar question (but not resolving this): find max value without aggregate operator in mysql

Upvotes: 0

Views: 2953

Answers (3)

Mark Byers
Mark Byers

Reputation: 838376

You can do this:

SELECT
    id,
    price,
    (SELECT MAX(price) FROM your_table) AS max_price
FROM your_table

I'm not sure why you'd want to return that value on every row though... I'd probably do this in two separate queries, or else use a UNION ALL:

SELECT id, price FROM your_table
UNION ALL
SELECT NULL, MAX(price) FROM your_table

Upvotes: 1

Jaydee
Jaydee

Reputation: 4158

I haven't tried this, but if you had a subquery that extracts the maximum price (so you get one row), and then do a cross join (cartesian product) with it. You should get something like what you want. I can't vouch for how fast this would be.

Upvotes: 0

Hammerite
Hammerite

Reputation: 22340

Maybe you could use a stored procedure like so:

CREATE PROCEDURE QueryWithMax ([parameter list if necessary])
BEGIN

    -- Obtain the maximum price
    DECLARE x INT UNSIGNED; -- change datatype if appropriate
    SET x = SELECT
                MAX(price)
            FROM
                ...
            WHERE
                ...
            ;

    -- Now do your query
    SELECT
        price,
        [other columns],
        x AS MaxPrice
    FROM
        ...
    WHERE
        ...
    GROUP BY
        ...
    ;

END

Upvotes: 0

Related Questions