rohit patil rohit patil - 4 years ago 79
SQL Question

selecting minimum of value with all columns

Table Result :

ID CAt date BS BD
19048 4 12/10/2013 132 68
19048 4 12/10/2013 2:43 138 78
19048 4 12/10/2013 2:44 128 74
19048 4 12/10/2013 2:45 140 80
19048 4 6/30/2014 22:07 146 88
19048 4 6/30/2014 22:07 148 88
19048 4 6/30/2014 22:08 138 86
19048 4 6/30/2014 22:08 132 80


I want min of bs and BD for each ID and date.

Expected output

ID CAt date NAME Value
19048 4 12/10/2013 BS 128
19048 4 12/10/2013 BD 68
19048 4 6/30/2014 BS 132
19048 4 6/30/2014 BD 80

Answer Source

You need Group By and Min aggregate.

Try this way

select ID,CAt, min([date]),Min(BS),Min(BD)
From yourtable 
Group by ID,CAt,cast([date] as Date)

cast([date] as Date) in group by is used to find the min BS and BD for each date

For second output you need to unpivot the data

SELECT ID,CAt,[date] = Min([date]),name,value = Min(value)
FROM   yourtable
       CROSS apply (VALUES ('bs',bs),
                           ('bd',bd)) cs (name, value)
GROUP  BY ID,CAt,Cast([date] AS DATE),name 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download