Find the largest value from column using SQL?

Question

I am using SQL where I have column having values like

A B
X1 2 4 6 8 10
X2 2 33 44 56 78 98 675 891 11111
X3 2 4 672 234 2343 56331
X4 51 123 232 12 12333

I want a query to get the value from col B with col A which has max count of values. I.e output should be

x2  2 33 44 56 78 98 675 891 11111

Query I use:

select max(B) from table

Results in

51 123 232 12 12333

What is the structure of your database table, i.e. is column B defined as VARCHAR or something else? — rochy_01, Feb 15 '18 at 14:28
Sample data A B x1 2 4 6 8 10 x2 2 33 44 56 78 98 675 891 11111 x3 2 4 672 234 2343 56331 x4 51 123 232 12 12333 — user3510412, Feb 15 '18 at 14:29
**Don't** store multiple values in a single field. Put them as individual records into another table and use the `PK` of the original table as `FK` into the new table. — axiac, Feb 15 '18 at 14:39

Sergey Kalinichenko · Accepted Answer · 2018-02-15T14:57:14.093

Assuming that both columns are strings, and that column B uses single space for separators and no leading/trailing spaces, you can use this approach:

SELECT A, B
FROM MyTable
ORDER BY DESC LENGTH(B)-LENGTH(REPLACE(B, ' ', ''))
FETCH FIRST 1 ROW ONLY

The heart of this solution is LENGTH(B)-LENGTH(REPLACE(B, ' ', '')) expression, which counts the number of spaces in the string B.

Note: FETCH FIRST N ROWS ONLY is Oracle-12c syntax. For earlier versions use ROWNUM approach described in this answer.

score 1 · Answer 2 · answered Feb 15 '18 at 15:01

In case there is more than one separating space or more then one row meets criteria do this: count number of spaces (or groups of spaces) in each row using regexp_count(). Use rank to find most (groups of) spaces. Take only rows ranked as 1:

^demo

select *
  from (select t.*, rank() over (order by regexp_count(b, ' +') desc) rnk from t)
  where rnk = 1

Find the largest value from column using SQL?

2 Answers2