SQL Query for Min and Max Values

Question

I have the following data in a table. The number of values in each row can vary and the number of rows could also vary.

The table has 1 column with csv formatted values. The values will always be numeric

Data
1,2
4
5,12, 10
6,7,8,9,10
15,17

I would like to end up with a temp table with the following

Data           Lowest            Highest
1,2               1                  2
4                 4                  4
5,12, 10          5                  12
6,7,8,9,10        6                  10
15,17             15                 17

Can anyone help with writing a sql query or function to achieve this

Refer this http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns — Ullas, Jun 25 '14 at 07:34

score 2 · Accepted Answer · answered Jun 25 '14 at 08:29

Instead of function, you can achieve by this

 ;WITH tmp 
     AS (SELECT A.rn,split.a.value('.', 'VARCHAR(100)') AS String 
         FROM   (SELECT Row_number() OVER(ORDER BY (SELECT NULL)) AS RN, 
                        Cast ('<M>' + Replace([data], ',', '</M><M>') + '</M>' AS XML) AS String 
                 FROM   table1) AS A 
                CROSS apply string.nodes ('/M') AS Split(a)) 
SELECT X.data,Tmp.lower,Tmp.higher 
FROM   (SELECT rn,Min(Cast(string AS INT)) AS Lower,Max(Cast(string AS INT)) AS Higher 
        FROM   tmp 
        GROUP  BY rn) Tmp 
       JOIN (SELECT Row_number() OVER(ORDER BY (SELECT NULL)) AS RN1,data 
             FROM   table1) X 
         ON X.rn1 = Tmp.rn

FIDDLE DEMO

Output would be:

Data              Lower              Higher
1,2               1                  2
4                 4                  4
5,12, 10          5                  12
6,7,8,9,10        6                  10
15,17             15                 17

score 1 · Answer 2 · edited Jun 25 '14 at 10:32

First create a user defined function to convert each row of 'DATA' column to a intermediate table as:

/****** Object:  UserDefinedFunction [dbo].[CSVToTable]******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
   (id int not null)
AS
BEGIN
    ;-- Ensure input ends with comma
    SET @InStr = REPLACE(@InStr + ',', ',,', ',')
    DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%,%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO @TempTab(id) VALUES (@VALUE)
END
    RETURN
END
GO

Function is explained further here.

Then Using Cross Apply we can get the desired output as:

With CTE as
(
    select 
    T.Data, Min(udf.Id) as [Lowest],Max(udf.Id) as [Highest]
    from 
    Test T
    CROSS APPLY dbo.CSVToTable(T.Data) udf
    Group By Data
)
Select * from CTE

Sample Code here...

What a Cross Apply does is : it applies the right table expression to each row from the left table and produces a result table with the unified result sets.

Azar · Answer 3 · 2014-06-25T08:04:29.900

0

Create table #temp1 (name varchar(100),value int )
Declare @len int
Select @len=(select max(LEN(name)-LEN(replace(name,',',''))) from table)
Declare @i int = 1 
while (@i<=@len+1)
begin
insert into #temp1
select name,PARSENAME(REPLACE(name,',','.'),@i) from table t 
set @i = @i+1

end

Select name,MIN(value) MINV,MAX(value) MAXV from #temp1 group by name

edited Jun 25 '14 at 08:04

answered Jun 25 '14 at 07:44

Azar

1,852
15
17

I get the max value for min and max values in the temp table – trailerman Jun 25 '14 at 07:49
same as original post – trailerman Jun 25 '14 at 07:56
check now jst a minimal change – Azar Jun 25 '14 at 08:04
execute it and u should be able to see the result without any changes for all – Azar Jun 25 '14 at 08:06
the last row in the results min and max are null,but the other rows min and max are correct – trailerman Jun 25 '14 at 08:07
the row with 6,7,8,9,10 have null values for min and max in the results – trailerman Jun 25 '14 at 08:09

score 0 · Answer 4 · answered Jun 25 '14 at 11:27

declare @Testdata table (  Data varchar(max))
insert @Testdata select   '1,2'
insert @Testdata select   '4'
insert @Testdata select   '5,12, 10'
insert @Testdata select   '6,7,8,9,10'



;with tmp(  DataItem, Data, RN1) as (
select   LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), ''),
    ROW_NUMBER()OVER(ORDER BY (SELECT NULL))AS RN1
from @Testdata
union all
select   LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), ''),RN1
from tmp
where Data > ''
)

Select x.data,t.Low,t.Up FROM
 (Select RN1,MIN(Cast(DataItem AS INT)) As Low,
 MAX(Cast(DataItem AS INT)) As Up 
    FROM tmp t GROUP BY t.RN1)t
JOIN (Select ROW_NUMBER()OVER(ORDER BY (SELECT NULL))AS RN,data from @Testdata)X
ON X.RN = t.RN1

SQL Query for Min and Max Values

4 Answers4