Getting the row-position when using order by

Question

I'm looking to get the row number of a horse in order by so i can see where it lies within the order.

I have used the following code

SELECT `odds`, (SELECT COUNT(*) FROM `tom_cards` WHERE `Racetime` = "14:30" Order By `Odds`) AS `position`, `Horse` FROM `tom_cards` WHERE `Racetime` = "14:30"

put this creates all the positions as 8

> odds  position    Horse
> 100.00    8   Central School
> 1.25  8   Coologue
> 1.10  8   Ma du Fou
> 33.00 8   Quintano
> 66.00 8   The Mobb
> 12.00 8   The Western Force
> 22.00 8   Youngdocgallagher
> 8.00  8   Tara Mac

I would like it to be in order of Odds from lowest to highest like below:

odds    position    Horse
100.00  8   Central School
1.25    2   Coologue
1.10    1   Ma du Fou
33.00   6   Quintano
66.00   7   The Mobb
12.00   4   The Western Force
22.00   5   Youngdocgallagher
8.00    3   Tara Mac

Answer 1

You can do what you want with a correlated subquery:

SELECT `odds`,
       (SELECT COUNT(*)
        FROM `tom_cards` tc2
        WHERE tc2.Racetime = tc.RaceTime and tc2.Odds <= tc.Odds
      ) AS `position`, `Horse`
FROM `tom_cards` tc
WHERE `Racetime` = '14:30';

I am guessing that if you have duplicate odds, then you want them to have the same "position". If so, you want the equivalent of a rank() (which is the ANSI standard function for this operation but MySQL does not support it):

SELECT `odds`,
       (SELECT 1 + COUNT(*)
        FROM `tom_cards` tc2
        WHERE tc2.Racetime = tc.RaceTime and tc2.Odds < tc.Odds
      ) AS `position`, `Horse`
FROM `tom_cards` tc
WHERE `Racetime` = '14:30';

Answer 2

Since select statement returns same value regardless of row, you should not use it there to display position. To display position try this

SET @position:=0;
SELECT `odds`, @position:=@position+1 AS `position`, `Horse` FROM `tom_cards` WHERE `Racetime` = "14:30" ORDER BY `odds`;

Demo