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:
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?
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.
Edit Edit: I love having a computer at work: This might help, this might too.