MySQL query finding max between columns where non-zero

Question

Suppose I have the following data:

data

Names X1 X2 X3
Jimmy  1  3  0
Mark   2  0  0
Jimmy  4  0  5

I am having trouble writing a unique query. What I would like return is the last number for each row that is non-zero. So the result would look like

Names Want
Jimmy    3
Mark     2
Jimmy    5

Is there a way to do this?

AdamMc331 · Accepted Answer · 2015-06-25 17:32:11Z

1

If you have a finite number of columns, you can use a CASE statement:

SELECT name, 
  CASE WHEN x3 <> 0 THEN x3
  WHEN x2 <> 0 THEN x2
  ELSE x1 END AS want
FROM myTable;

Here is an SQL Fiddle example.

answered Jun 25, 2015 at 17:32

AdamMc331

16.7k11 gold badges78 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AdamMc331 Over a year ago

@theamateurdataanalyst Glad to help! If this is the answer you stick with, feel free to upvote and accept it so future readers will know what solved your problem.

Collectives™ on Stack Overflow

MySQL query finding max between columns where non-zero

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related