Page 1 of 1
Unable to query MAX value and other data from the row.
Posted: Fri Apr 13, 2007 8:14 am
by impulse()
I want to search the database for the highest value in a selected field but also retrieve the data from that row.
This works fine as expected but if I also want to see other data related to that entry the query fails. Such as :
Code: Select all
SELECT MAX(list_price), order_no
FROM table
Could somebody point out why please.
Regards,
Posted: Fri Apr 13, 2007 8:21 am
by feyd
The error the database gives you would tell you why. I will guess that it will refer to needing a grouping for the aggregate to work now.
Posted: Fri Apr 13, 2007 9:15 am
by impulse()
If I run:
Code: Select all
SELECT MAX(list_price)
FROM TABLE
GROUP BY <other column(s) I want for that row>
It still only returns the list_price column with no errors, as if I never requested for the other data. I just want 1 row from the DB which in 1 field has the highest value from the whole DB.
Posted: Fri Apr 13, 2007 9:33 am
by feyd
Code: Select all
SELECT
*
FROM
yourTable
WHERE
list_price = MAX(list_price)
maybe.
Posted: Fri Apr 13, 2007 9:49 am
by timvw
Under the assumption there is ONE row with the max value...
Code: Select all
SELECT * FROM table WHERE column = (SELECT MAX(column) FROM table);
Posted: Fri Apr 13, 2007 11:38 am
by veridicus
Code: Select all
SELECT MAX(list_price), order_no
FROM table_name
ORDER BY MAX(list_price) DESC
LIMIT 1