Hi Need query for min value in row

Transact-SQL
1

hi every one need query for selecting minimum value from the following table structure

Department A B C D
1.0000 0.0000 0.1677 0.0000 0.0000
2.0000 0.0000 0.1677 0.0000 0.0000
3.0000 0.0000 0.1677 0.0000 0.0000
4.0000 0.0000 0.0000 0.0000 0.0000
5.0000 0.1450 0.1648 0.1850 0.2150
6.0000 0.1700 0.1853 0.1650 0.1620

required minimum value for column 1 from 1st row

2

what do you mean .. please b clear ..solution is very very easy

image

or this

image

3

i want the minimum value against each department i.e. 1 ,2,3 with minimum Value in that row against the department for example

result for Department should be
1 minimum value A , C, D
5 minimum value A

4

or for example from the following table

YEAR JAN FEB MAR APR MAY JUNE JULY AUG SEPT OCT NOV DEC
2018 49.7 45.8 54 58.6 70.8 71.9 82.8 79.1 NULL NULL NULL NULL
2017 43.7 46.6 51.6 57.3 67 72.1 78.3 81.5 73.8 61.1 51.3 45.6
2016 49.1 53.6 56.4 65.9 68.8 73.1 76 79.5 69.6 60.6 56 41.9
2015 50.3 54.5 57.9 59.9 68 78.9 82.6 79 68.5 63.6 49.4 47.1

i need month in which the value is minimum against the each year

5

hi

hope this helps :slight_smile:

there are several ways to do this !!!

i tried one way

please click arrow to the left for DROP CREATE Data
And the SQL

Data and SQL 
CREATE TABLE #data 
(
YEAR	INT ,
JAN	    decimal(5,2) NUll,
FEB	decimal(5,2) NUll,
MAR	decimal(5,2) NUll,
APR	decimal(5,2) NUll ,
MAY	decimal(5,2) NUll,
JUNE	decimal(5,2) NUll ,
JULY	decimal(5,2) NUll ,
AUG	decimal(5,2)  NUll,
SEPT	decimal(5,2)  NUll,
OCT	decimal(5,2) NUll ,
NOV	decimal(5,2)  NUll,
DEC DECIMAL(5,2)  NUll
)
GO 

INSERT INTO #data SELECT 2018,49.7,45.8,54	,58.6,70.8,71.9	 ,82.8,79.1	  , NULL,NULL,NULL,NULL
INSERT INTO #data SELECT 2017,43.7,46.6,51.6,57.3,67	,72.1,78.3,81.5	  , 73.8,61.1,51.3,45.6
INSERT INTO #data SELECT 2016,49.1,53.6,56.4,65.9,68.8,73.1	 ,76	,79.5,69.6	,60.6,56	,41.9
INSERT INTO #data SELECT 2015,50.3,54.5,57.9,59.9,68	,78.9,82.6,79	  , 68.5,63.6,49.4,47.1


SELECT 'data',* FROM #data 
GO 

; WITH cte AS 
(
SELECT year , JAN AS Value , 'JAN' 	AS month from #data 
UNION ALL 					 
SELECT year , FEB AS Value 	, 'FEB' from #data 
UNION ALL 					
SELECT year , MAR AS Value 	, 'MAR' from #data 
UNION ALL 					 
SELECT year , APR AS Value 	, 'APR' from #data 
UNION ALL 					 
SELECT year , MAY AS Value 	, 'MAY' from #data 
UNION ALL 					
SELECT year , JUNE AS Value , 'JUNE'	 from #data 
UNION ALL 					 
SELECT year , JULY AS Value , 'JULY'	 from #data 
UNION ALL 					
SELECT year , AUG AS Value 	, 'AUG' from #data 
UNION ALL 					 
SELECT year , SEPT AS Value , 'SEPT'	 from #data 
UNION ALL 					
SELECT year , OCT AS Value 	, 'OCT' from #data 
UNION ALL 					
SELECT year , NOV AS Value , 'NOV'	 from #data 
UNION ALL 					
SELECT year , DEC AS Value 	, 'DEC' from #data 
), cte_rn AS 
(
SELECT ROW_NUMBER() OVER(PARTITION BY year ORDER BY ISNULL(value,0) ) AS rn, year,value,month FROM cte  
WHERE value IS NOT NULL 
)
SELECT 'SQL Output',* FROM cte_rn WHERE rn =1 


DROP TABLE #data

image
image741×410 13.2 KB

1 Like
6

thanks

7

Any other Solution coz data is too large this Code Required Manual entries
i tried the following query

SELECT [ID],
            (
                SELECT MIN([value].[MinValue])
                FROM
                (
                    VALUES
                        ([Col1]),
                        ([Col1]),
                        ([Col2]),
                        ([Col3])
                ) AS [value] ([MinValue])
           ) AS [MinValue]
FROM Table;

but unable to get result like the result your query have

8

Since we do not have access to your database, we have to generate sample data on our SQL Server to give you an answer. Just change the sample data tables listed here with # to your database tables or give us direct access to your SQL Server.

department _year months Orders _year _min
Services 2015 DEC 47.1 2015 47.1
HR 2016 DEC 41.9 2016 41.9
IT 2017 JAN 43.7 2017 43.7
Accounting 2018 FEB 45.8 2018 45.8 

create table #unpivot(department varchar(50), _year int, months varchar(10), Orders decimal(4,1) )

insert into #unpivot
SELECT department, _YEAR, Months, Orders
FROM   
   (
	   SELECT department, _YEAR, JAN, FEB, MAR, APR, MAY, JUNE, JULY, AUG, SEPT, OCT,NOV,DEC
	   FROM #data
   ) p  
UNPIVOT  
   (Orders FOR Months IN   
      (JAN, FEB, MAR, APR, MAY, JUNE, JULY, AUG, SEPT, OCT,NOV,DEC)  
)AS unpvt

order by _YEAR, Months

select * 
 from #unpivot a
 join (
		select _year, min(orders) as _min
		  from #unpivot a
		  group by _year
  ) b on a._year = b._year
  and a.Orders = b._min
1 Like