selecting minimum of value with all columns

Question

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

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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

selecting minimum of value with all columns

Sign up