CODEWITHSUNDEEP
mysql
richie
richie

Reputation: 801

How do I get the maximum value in a comma separated field in MySQL?

I have a MySQL table that has a field of comma separated values. How do I get the maximum value?

Example:

CREATE TABLE `test`.`scale` (`scale` VARCHAR(500));

INSERT INTO `test`.scale 
VALUES 
('1,5,0.3,0.8,1'), 
('0.5,0.7,0.1,0.3'), 
('7,0.5,0.7,4');

scale:

1,5,0.3,0.8,1
0.5,0.7,0.1,0.3
7,0.5,0.7,4

answer:

5
0.7
7

How do I select the max value in this string?

Unfortunately I can't change the way the previous person implemented the comma separated solution. I hope there is a MySQL function that can help me out, otherwise I’ll have to do some messy PHP stuff.

Upvotes: 0

Views: 2950

Answers (3)

G-Agnes
G-Agnes

Reputation: 101

SELECT GREATEST(`scale`,0) as greatest_scale FROM `scale`;

That doesn't answer the question entirely, but this works at least for lists of integers.

Upvotes: -1

user1285324
user1285324

Reputation:

create cursor withiin a procedure to fetch each row and insert into a new table with seperating every comma sepertated field using nested substring_index and then select max from this new table, set a loop using cursor to repeate this for whole table and store the max of each row in a new table.and at the end select * from final table.

Upvotes: 0

octern
octern

Reputation: 4868

I don't believe SQL has a function for splitting strings. Here's some code that claims to do it, but I haven't tested it.

The deeper reason for this is that SQL desperately wants you to store your data in normalized form. In this particular case, this means that every field should represent a single piece of data about the subject of the row.

Essentially you'd store scale and answer in two separate tables, which you could match with one another using an ID for the question each one belongs to. Scale would have one row per scale option, not one row per scale:

OPTIONS                     ANSWERS
question | scaleoption      question | answer
---------|------------      ---------|-------
       1 | 1.5                    1  | 5
       1 | .3                     2  | .7
       1 | .8               ... 
       1 | 1
       2 | .5
       2 | .7
...

You could then use the query select max(scaleoption) from options where question = 2 to get the maximum option for question 2. This format also lets you do things like find all questions where one of the options is 1, or get a count of how many options a particular question has. It looks more complicated but it's a flexible, extendible, portable way to do things.

If you want to use your current format, you'll have to return the entire string and break it up in some other programming language.

Upvotes: 2

Related Questions