MAX query help

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
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

MAX query help

Post by shiznatix »

I have a query that I am using to get all of the data from a table and then I sort through it using PHP to get the row with the highest value for "gross_rake" then save that row as the PHP variables I will use. My query looks a bit like this:

Code: Select all

SELECT
	`rb_raketracking`.`data_date`,
	`rb_raketracking`.`date_added`,
	`rb_raketracking`.`net_rake`,
	`rb_raketracking`.`gross_rake`,
	`rb_raketracking`.`deductions`,
	`rb_raketracking`.`is_final`
FROM `rb_raketracking`
WHERE
	(fk_room_id = "7")
	AND (room_username = 'some_username' )
	AND (data_date LIKE "2007-11%")
ORDER BY data_date ASC
But after thinking about it I realized that I could save a lot of time by just selecting 1 row and making sure it is the MAX for gross_rake that fits the WHERE conditions. I tried throwing a MAX() around the gross_rake field but that just spat out an error so obviously I am doing it wrong. How do I do it correctly?
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Post by Chalks »

should be something like:

Code: Select all

SELECT
    MAX(`rb_raketracking`.`gross_rake`)
   `rb_raketracking`.`data_date`, 
   `rb_raketracking`.`date_added`, 
   `rb_raketracking`.`net_rake`, 
   `rb_raketracking`.`deductions`, 
   `rb_raketracking`.`is_final`
FROM `rb_raketracking`
WHERE 
   (fk_room_id = "7") 
   AND (room_username = 'some_username' ) 
   AND (data_date LIKE "2007-11%") 
I think. I had this same problem a few weeks ago, and I can't exactly remember my solution. As soon as I get back home from work, I'll look at my old code.


Edit: after researching this a bit, my code won't work. I'm off work in 45 minutes... I'll have an answer then. :D
Edit Edit: I love having a computer at work: This might help, this might too.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Why not just use "order by gross_rake desc limit 1"?

Maybe I am missing something?
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Post by Chalks »

astions wrote:Why not just use "order by gross_rake desc limit 1"?

Maybe I am missing something?
You're not. I just looked at my old code and that's exactly what I did. I was way overthinking this one.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

wow so was I over thinking this. Silly me. Thanks
Post Reply