Mysql & # 8594; Getting the position of a string when using order
I searched the forum and found several solutions for my problem, which I put together into a MySQL query.
I'm looking to get the horse's line number so that I can see where it is within the order.
I 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 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 as shown 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
Can anyone please help?
source to share
You can do what you want with the 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 assuming that if you have duplicate odds, you want them to have the same "position". If so, you need an equivalent rank()
(which is an 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';
source to share
Since the select statement returns the same value regardless of the row, you shouldn't use it there to display the 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`;
source to share
Solution 1:
You can change your query like this:
SELECT `odds`, (SELECT COUNT(*) FROM `tom_cards` WHERE `Racetime` = t2.`Racetime` and `odds` <= t2.`odds`) AS `position`, `Horse` FROM `tom_cards` as t2 WHERE `Racetime` = "14:30"
But you need to be careful here, if multiple records have the same time (e.g. 14:30) then each will have the same position value. Thus, it may not be exactly the same as the position in Order By.
Also, due to the subquery, this query will not perform well on large datasets.
Solution 2:
Use a variable as others have already recommended.
The problem is that the order in which the expressions in selects are evaluated is not defined, so you need to be careful if you have any further plans with position information. For example. see here or here
Solution 3:
Your question is tagged as a php question, so you can also just query the result set and calculate the position in php. This seems to be the most effective.
source to share