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.

Code: Select all

SELECT MAX(list_price)
FROM table

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