473,325 Members | 2,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

displaying result in a single row

26
I have a table called 'animal_prize' in a Myswl database.
The columns / fields are:
'animal_event_id' contains numbers such as 1
'animal_place' contains numbers from 1 to 10
'animal_prize_money' 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.
Jul 13 '10 #1
10 1729
zorgi
431 Expert 256MB
Have a look at MySQL commands ORDER BY and LIMIT
Jul 13 '10 #2
ozchadl
26
Those commands were not that helpful.
The '1st', '2nd', and '3rd" gets the amount from 'animal_prize_money'.
The '1st', '2nd', and '3rd" is from 'animal_place'.

The result should be somehting like
'animal_event_id' '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.
Jul 14 '10 #3
TheServant
1,168 Expert 1GB
Expand|Select|Wrap|Line Numbers
  1. 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 ;)
Jul 14 '10 #4
ozchadl
26
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_money else null end as first,
case when animal_place = 2 then animal_prize_money else null end as second,
case when animal_place = 3 then animal_prize_money else null end as third
from animal_prize_money;

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


with my code above I get this:
'animal_event_id'-----'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.
Jul 15 '10 #5
TheServant
1,168 Expert 1GB
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.
Jul 15 '10 #6
ozchadl
26
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
Jul 15 '10 #7
johny10151981
1,059 1GB
Expand|Select|Wrap|Line Numbers
  1. table con_res
  2. mysql> select *from cont_res;
  3. +--------+-----------+-------+
  4. | con_id | animal_id | prize |
  5. +--------+-----------+-------+
  6. |      1 |         1 |    10 |
  7. |      1 |         2 |    30 |
  8. |      1 |         3 |    40 |
  9. |      1 |         4 |    20 |
  10. |      2 |         1 |    20 |
  11. |      2 |         2 |    21 |
  12. |      2 |         3 |    44 |
  13. |      2 |         4 |    41 |
  14. |      3 |         1 |    10 |
  15. |      3 |         2 |    20 |
  16. |      3 |         3 |   120 |
  17. |      3 |         4 |    18 |
  18. |      4 |         1 |    52 |
  19. |      4 |         2 |    19 |
  20. |      4 |         3 |    17 |
  21. |      4 |         4 |    44 |
  22. +--------+-----------+-------+
  23. 16 rows in set (0.00 sec)
  24.  
query
Expand|Select|Wrap|Line Numbers
  1. select distinct A.con_id as cid,
  2. (select animal_id from cont_res where con_id=cid order by prize desc limit 0,1) as 1st,
  3. (SELECT animal_id from cont_res where con_id=cid order by prize desc limit 1,1) as second,
  4. (SELECT animal_id from cont_res where con_id=cid order by prize desc limit 2,1) as third
  5.  from cont_res A
  6.  
result
Expand|Select|Wrap|Line Numbers
  1. +------+------+--------+-------+
  2. | cid  | 1st  | second | third |
  3. +------+------+--------+-------+
  4. |    1 |    3 |      2 |     4 |
  5. |    2 |    3 |      4 |     2 |
  6. |    3 |    3 |      2 |     4 |
  7. |    4 |    1 |      4 |     2 |
  8. +------+------+--------+-------+
  9. 4 rows in set (0.00 sec)
  10.  
Not a pretty solution :(
Jul 15 '10 #8
ozchadl
26
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_id' would be your con_id
'animal_place' would be your animal_id
'animal_prize_money' would be your prize


Thanks for your help
Jul 15 '10 #9
ozchadl
26
I managed to get it working.
Thanks
Jul 16 '10 #10
TheServant
1,168 Expert 1GB
@ozchadl
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.
Jul 16 '10 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Clouds® | last post by:
Hi all, Strange topic, I'll explain it: I have a page with a for-loop that takes a long time to execute. In this loop, a line of data is printed using printf (or echo). I currently see the...
2
by: Andrew | last post by:
Hi, How do I display a single image in a repeater control ? I only have 1 picture to display for ONE of my records. TIA. Andrew.
2
by: lifeshortlivitup | last post by:
I had to create a program that allows the user to input a temp and then click on either the convert to fahrenheit or convert to celsius button and then display that result within the textbox that...
2
by: Pramodhegde | last post by:
Hi, I have a result set, which is in the form of a list of strings. I want to display it on the application using a gridview. If i bind the list as a source for the gridview, the result is...
5
by: Harlequin | last post by:
Hi there, I'm something of a newcomer to MS Access so it's more than likely that my question is extremely elementary. I have a form whose record source is a single table in my database. It's...
2
by: MIkeC | last post by:
I am new to quite new vb net 2005 and want to read a sql server table and load the result set directly into excel. I want the worksheet to be opened and display the column headers and data from the...
19
by: RossGK | last post by:
I'm a bit new to javascript - as will be obvious below. I'm using an XMLHttpRequest to get a bit of data from server (django), and it works nicely for single events. But my eventual outcome...
1
by: codehelp55 | last post by:
function abc() { var tstr1 = new Array(); var vari=50; var htmlText =''; for(var i=0; i<vari; i++) { tstr1.push(); }
33
by: mike6256 | last post by:
Hi all I am extremely new to php and need some help I found a code that Atli addressed in a 11/23/07 thread and have been working with it-all is great. My main question is how do I search for a...
3
by: GinaMarano | last post by:
I run many queries per day. The results are usually 1 row with many, many columns. Is there a trick/free tool to display this row as 1 column instead? example: col 1 col 2 col 3 col 4...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.