CODEWITHSUNDEEP
phpmysql
emma perkins
emma perkins

Reputation: 769

MYSQL -> Getting top value of each "group by"

I would like to get the shortest odds value in each race - or in this case the favorite

my database looks like this

Racetime Odds

13:05     2
13:05     2.4
13:05     3
13:05     5
13:30     3
13:30     5
13:30     9
13:30     13.5
14:00     1.14
14:00     1.19
14:00     2
14:00     4

im trying to do the sql so its something around

Select Odds from `tom_cards` where `Odds` < '2.5' Group by Racetime limit 1

But this just gives me the 1 result due to the top 1 at the end and im unsure how to group it. The result i would like would be

Racetime Odds

13:05     2
14:00     1.14

Then i can pull the horse and the time

Upvotes: 0

Views: 82

Answers (3)

Shivam
Shivam

Reputation: 720

You have to make the changes in your query. please check this and let me know if any help required.

SELECT min(Odds)
FROM tom_cards 
GROUP BY Racetime
HAVING SUM(Odds < 2.5) > 0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Presumably, you want an aggregation query:

select racetime, min(odds)
from tom_cards
where odds < 2.5
group by racetime;

Note: when putting numeric constants into queries, there is no need for single quotes. If the odds are being stored as a string -- which is a really, really bad idea -- then you will need to convert them to numbers to get the minimum:

select racetime, min(odds + 0)
from tom_cards
where odds + 0 < 2.5
group by racetime;

Upvotes: 1

juergen d
juergen d

Reputation: 204766

Select min(Odds)
from `tom_cards` 
Group by Racetime
Having sum(`Odds` < 2.5) > 0

Upvotes: 0

Related Questions