15,881,248 members
See more:
I am having hard time thinking for a posible solution for this problem: i have one table that has value as
C#
```name    value
A        0
A        10
A        20
B        0
B        50
B        70
B        100```

Requirement: i) find min and max value for each name i.e.
C#
```name min max
A     0   20
B     0   100```

then divide the min and max to 10 equal parts and display like:
C#
```name range1 range2
A      0       2
A      2       4
A      4       6
A      6       8
A      8       10
A      10      12
A      12      14
A      14      16
A      16      18
A      18      20```

and similarly for B.

I tried something like this:
C#
```SELECT
name,
MIN,
MAX,
(MAX - MIN)/10 PARTITION_VALUE,
FROM(SELECT
name,
MIN(value) MIN,
MAX(value) MAX
FROM [table]
GROUP BY name) A```

calculated min value, max value and the partition_value that will help in defining the range.. i.e. dividing min and max value in 10 different parts.
I am just unable to proceed further.

Thanks
Posted
Updated 30-Apr-15 19:55pm
v2

## Solution 1

HI here is the sql query from which you can get max & min values of names

`select name, (select MIN(value) from table1 b where b.name=a.name) as mini,(select MAx(value) from table1 c where c.name=a.name) as maxi from table1 a group by name`

thanks.

## Solution 2

hi Deepak,

Thank you for the answer, but my desired output is something else.
I need to first find min and max values as per name (which is the output from your query).
After that based on those value, I need to create 10 rows with equal partitions for each name.
its like A has min as 0 and max as 20.
so its partition would look like
A 0
A 2
A 4
A 6
A 8
A 10
A 12
A 14
A 16
A 18
A 20

After doing this, i require the output as:

A 0 2
A 2 4
A 4 6
A 6 8
A 8 10
A 10 12
A 12 14
A 14 16
A 16 18
A 18 20

## Solution 3

SQL
```WITH DATA_RANGE AS(
SELECT NAME, DATA_VALUE, LEAD(DATA_VALUE) OVER (PARTITION BY NAME ORDER BY DATA_VALUE) NEXT_VALUE FROM
(
SELECT NAME,MIN_VAL V1,MIN_VAL+PARTITION V2,MIN_VAL+PARTITION*2 V3,MIN_VAL+PARTITION*3 V4,MIN_VAL+PARTITION*4 V5,MIN_VAL+PARTITION*5 V6
,MIN_VAL+PARTITION*6 V7,MIN_VAL+PARTITION*7 V8,MIN_VAL+PARTITION*8 V9,MIN_VAL+PARTITION*9 V10,MAX_VAL V11
FROM(
SELECT NAME,MIN_VAL,MAX_VAL,(MAX_VAL-MIN_VAL)/10 PARTITION
FROM(
SELECT NAME,MAX(VALUE) MAX_VAL, MIN(VALUE) MIN_VAL FROM TAB
GROUP BY NAME) A) A) A
UNPIVOT
(DATA_VALUE
FOR DATA_TYPE IN (V1,V2,V3,V4,V5,V6,V7,V8,V9,V10,V11))
ORDER BY NAME, DATA_VALUE)
SELECT NAME, DATA_VALUE, NEXT_VALUE FROM DATA_RANGE
WHERE NEXT_VALUE IS NOT NULL
ORDER BY NAME,DATA_VALUE```