Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
impulse()
Forum Regular
Posts: 748 Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:
Post
by impulse() » Fri Apr 13, 2007 8:14 am
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,
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Fri Apr 13, 2007 8:21 am
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() » Fri Apr 13, 2007 9:15 am
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.
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Fri Apr 13, 2007 9:33 am
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 » Fri Apr 13, 2007 9:49 am
Under the assumption there is ONE row with the max value...
Code: Select all
SELECT * FROM table WHERE column = (SELECT MAX(column) FROM table);
veridicus
Forum Commoner
Posts: 86 Joined: Fri Feb 23, 2007 9:16 am
Post
by veridicus » Fri Apr 13, 2007 11:38 am
Code: Select all
SELECT MAX(list_price), order_no
FROM table_name
ORDER BY MAX(list_price) DESC
LIMIT 1