Order by highest value among columns

maroviod · #1 04-Dec-2006, 15:54

Hi,
Let me explain the scenario.
I have a PHP MySql web based system where different people are supposed to rate an artist performance. To accomplish this task, each of these five judges should enter a value (for example: 5) in one field. So i ended up with a table with five columns, labeled "judge1", judge2", and so on.
This is my table:
Table structure for table `artist_performances`

Code:

CREATE TABLE `artist_performances` (
  `id_art` int(5) NOT NULL auto_increment,
  `artist_name` varchar(255) default NULL,
  `judge1` varchar(8) default NULL,
  `judge2` varchar(8) default NULL,
  `judge3` varchar(8) default NULL,
  `judge4` varchar(8) default NULL,
  `judge5` varchar(8) default NULL,
  `total` varchar(8) default NULL,
  PRIMARY KEY  (`id_art`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

Now, what i need is get the values entered by these judges and sort them in ascending order. Lets take these values for example:

judge1 entered "5"
judge2 entered "2"
judge3 entered "9"
judge4 entered "1"
judge5 entered "3"

i need to create a recordset that retrieve that info in this order:

judge3 entered "9"
judge1 entered "5"
judge5 entered "3"
judge2 entered "2"
judge4 entered "1"

Se the point?, How can i do this?
Thaks in advance.

dollarbd · #2 27-Jan-2007, 04:38

Can be done if u steel want to. please let me know........

maroviod · #3 08-Feb-2007, 13:06

Hi, that would be great!.
How is that done?

TurboPT · #4 10-Feb-2007, 22:45

Q1. Are you sure you want ASCending order? What you have listed in the example result [in post #1] is in DEScending order. (by the value)

Q2. Do you really need the 'entered' word in each line -- even if you could get output to look something like this?
(the format is a little broken here [I couldn't keep it aligned in the posting], but imagine it how MySQL displays its output aligned)
+--------+---------+
| voter | score |
+--------+---------+
| judge3 | 9 |
| judge1 | 5 |
| judge5 | 3 |
| judge2 | 2 |
| judge4 | 1 |
+--------+---------+

maroviod · #5 11-Feb-2007, 02:18

Hi,
look, i don't care about the order right now, the idea is to get the values listed so i could get rid of the lowest and highest values. If i were working with different records i could use LIMIT but in this particular case, it's a single record that stores 5 different values, the goal is to sum the values that lists between the lowest and the highest ones.
When i say "get rid of" i mean not use these values, i don't really need to delete them from the DB, i just don't want to use them.
Maybe there's a different way of achieving this. Maybe there's a way to sum the middle values and that's it.
I'm open to all kind of resolutions at this point.
Thx.

TurboPT · #6 11-Feb-2007, 09:48

This will get the values similar to what you wanted in post #1, but with the output mentioned in post #4.
query:

Code:

SELECT voter, score FROM (
      SELECT "judge1" AS voter, judge1 AS score FROM artist_performances 
UNION SELECT "judge2" AS voter, judge2 AS score FROM artist_performances 
UNION SELECT "judge3" AS voter, judge3 AS score FROM artist_performances
UNION SELECT "judge4" AS voter, judge4 AS score FROM artist_performances 
UNION SELECT "judge5" AS voter, judge5 AS score FROM artist_performances 
ORDER BY score DESC ) AS bad_table_design;

output: (thanks for the format tip, WaltP!)

Code:

+--------+-------+
| voter  | score |
+--------+-------+
| judge3 | 9     |
| judge1 | 5     |
| judge5 | 3     |
| judge2 | 2     |
| judge4 | 1     |
+--------+-------+

I'll let you ponder the elimination part that you mentioned in post #5, for this basically achieves what you wanted in post #1. However, should you get stuck, post another reply.

maroviod · #7 11-Feb-2007, 12:44

Great, i will give it a try before posting and will let you know.
Thank you very much.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
CPanel Hosting, Teamspeak 2 Hosting, SHOUTcast Hosting, As Low As $2.00 per Month	Kalypsoweb	Web Hosting Advertisements & Offers	0	30-Jul-2006 17:00
Code Contest	Paramesh	Miscellaneous Programming Forum	76	30-Jun-2006 13:18
Coding Contest #1	davis	Miscellaneous Programming Forum	0	12-Jun-2006 09:29
Two virtual hosts, cgi script behaves differently on each	blimbo	Apache Web Server Forum	0	04-Aug-2004 10:35
Sorting columns by selection	dean	MySQL / PHP Forum	1	03-Oct-2003 07:29

Order by highest value among columns

Re: Order by highest value among columns

Re: Order by highest value among columns

Re: Order by highest value among columns

Re: Order by highest value among columns

Re: Order by highest value among columns

Re: Order by highest value among columns