displaying result in a single row

[ozchadl]

I have a table called 'animal_prize' in a Myswl database.
The columns / fields are:
'animal_event_i d' contains numbers such as 1
'animal_place' contains numbers from 1 to 10
'animal_prize_m oney' contains numbers such as 10


I am trying to get the results so that it appears like the following:
animal_event_id 1st 2nd 3rd
1 30 20 10
2 40 30 20


It is only meant to show the top 3 places with the prize money for that place, in each event.

Thanks for your help.

[zorgi]

Have a look at MySQL commands ORDER BY and LIMIT

[ozchadl]

Those commands were not that helpful.
The '1st', '2nd', and '3rd" gets the amount from 'animal_prize_m oney'.
The '1st', '2nd', and '3rd" is from 'animal_place'.

The result should be somehting like
'animal_event_i d' '1st' '2nd' '3rd'
1 30 20 10


If the place is 1, then "1st", should say something like 30.

If the place is 2, then "2nd", should say something like 20.

If the place is 3, then "3rd", should say something like 30.

Thanks for you help.

[TheServant]

SELECT animal_prize_money FROM animal_prize WHERE animal_event_id=1 ORDER BY animal_prize_money DESC LIMIT 3

I think that zorgi's suggestions are appropriate. That will only select the top 3 for event 1, but it is easy to have two ORDER BY fields, but that might involve you reading up on it ;)

[ozchadl]

That code is not for what I am trying to do.

This is my code so far:
Select animal_event_id ,
case when animal_place = 1 then animal_prize_mo ney else null end as first,
case when animal_place = 2 then animal_prize_mo ney else null end as second,
case when animal_place = 3 then animal_prize_mo ney else null end as third
from animal_prize_mo ney;

I want to end up with a table which is like:
'animal_event_i d'-----'1st'-----'2nd'-----'3rd'
1--------------------- 30--------20--------10
2----------------------40--------30--------20


with my code above I get this:
'animal_event_i d'-----'1st'-----'2nd'-----'3rd'
1--------------------- 30--------null------null
1----------------------null------20--------null
1--------------------- null------null------10
2--------------------- 30--------null------null
2----------------------null------20--------null
2--------------------- null------null------10

as so on

Thanks for you help.

[TheServant]

What does my code end up with?

I believe mine will end up with an array such as [30,20,10] which for even 1 is 1st 2nd and 3rd place.

[ozchadl]

There are multiply events.
The prize money amounts do vary

my code is similar to what one of the replies is on

http://forums.mysql.com/read.pho?10,301759,301767

My question is similar to that question ask on

http://forums.mysql.com/read.pho?10,301759,301759

[johny10151981]

table con_res
mysql> select *from cont_res;
+--------+-----------+-------+
| con_id | animal_id | prize |
+--------+-----------+-------+
|      1 |         1 |    10 |
|      1 |         2 |    30 |
|      1 |         3 |    40 |
|      1 |         4 |    20 |
|      2 |         1 |    20 |
|      2 |         2 |    21 |
|      2 |         3 |    44 |
|      2 |         4 |    41 |
|      3 |         1 |    10 |
|      3 |         2 |    20 |
|      3 |         3 |   120 |
|      3 |         4 |    18 |
|      4 |         1 |    52 |
|      4 |         2 |    19 |
|      4 |         3 |    17 |
|      4 |         4 |    44 |
+--------+-----------+-------+
16 rows in set (0.00 sec)

query

select distinct A.con_id as cid,
(select animal_id from cont_res where con_id=cid order by prize desc limit 0,1) as 1st,
(SELECT animal_id from cont_res where con_id=cid order by prize desc limit 1,1) as second,
(SELECT animal_id from cont_res where con_id=cid order by prize desc limit 2,1) as third
 from cont_res A

result

+------+------+--------+-------+
| cid  | 1st  | second | third |
+------+------+--------+-------+
|    1 |    3 |      2 |     4 |
|    2 |    3 |      4 |     2 |
|    3 |    3 |      2 |     4 |
|    4 |    1 |      4 |     2 |
+------+------+--------+-------+
4 rows in set (0.00 sec)

Not a pretty solution :(

[ozchadl]

Could you please explain
select distinct A.con_id as cid
what is A


Could you please explain
from cont_res A
what is A

I know cont_res is your table name
'animal_event_i d' would be your con_id
'animal_place' would be your animal_id
'animal_prize_m oney' would be your prize


Thanks for your help

[ozchadl]

I managed to get it working.
Thanks

[TheServant]

I managed to get it working.
Thanks

If you used a different solution to the one provided by johny10151981, please can you post it so that others may benefit from this as well.