Page 1 of 1

MAX query help

Posted: Wed Dec 19, 2007 5:09 am
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?

Posted: Wed Dec 19, 2007 12:18 pm
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.

Posted: Wed Dec 19, 2007 12:51 pm
by Benjamin
Why not just use "order by gross_rake desc limit 1"?

Maybe I am missing something?

Posted: Wed Dec 19, 2007 1:16 pm
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.

Posted: Thu Dec 20, 2007 6:02 am
by shiznatix
wow so was I over thinking this. Silly me. Thanks