Unable to query MAX value and other data from the row.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Unable to query MAX value and other data from the row.

Post 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,
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT
  *
FROM
  yourTable
WHERE
  list_price = MAX(list_price)
maybe.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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);
User avatar
veridicus
Forum Commoner
Posts: 86
Joined: Fri Feb 23, 2007 9:16 am

Post by veridicus »

Code: Select all

SELECT MAX(list_price), order_no 
FROM table_name
ORDER BY MAX(list_price) DESC
LIMIT 1
Post Reply