Prakazz Prakazz - 27 days ago 8
SQL Question

Max and Min value's corresponding records

I have a scenario to get the respective field value of "Max" and "Min" records
Please find the sample data below

-----------------------------------------------------------------------
ID Label ProcessedDate
-----------------------------------------------------------------------
1 Label1 11/01/2016
2 Label2 11/02/2016
3 Label3 11/03/2016
4 Label4 11/04/2016
5 Label5 11/05/2016


I have the "ID" field populated in another table as a foreign key. While querying those records in that table based on the "ID" field I need to get the "Label" field of "Max" Processed date and "Min" processed date.

-----------------------------------------------------------------------
ID LabelID GroupingField
-----------------------------------------------------------------------
1 1 101
2 2 101
3 3 101
4 4 101
5 5 101
6 1 102
7 2 102
8 3 102
9 4 102


And the final result set I expect it to look something like this.

-----------------------------------------------------------------------
GroupingField FirstProcessed LastProcessed
-----------------------------------------------------------------------
101 Label1 Label5
102 Label1 Label4


I have 'almost' managed to get this above result using rank function but still not satisfied with it. So I am looking if someone can provide me with a better option.

Thanks,
Prakazz

Answer

You can use SQL Row_Number() function using Partition By as follows with a combination of Group By

;with cte as (
    select 
        t.Label, t.ProcessedDate,
        g.GroupingField,
        ROW_NUMBER() over (partition by GroupingField Order By ProcessedDate ASC) minD,
        ROW_NUMBER() over (partition by GroupingField Order By ProcessedDate DESC) maxD

    from tbl t 
    inner join GroupingFieldTbl g
    on t.ID = g.LabelID
 )
select GroupingField, max(FirstProcessed) FirstProcessed, max(LastProcessed) LastProcessed
from (
select
     GroupingField,
     FirstProcessed = CASE when minD = 1 then Label else null end,
     LastProcessed = CASE when maxD = 1 then Label else null end
from cte 
where 
    minD = 1 or  maxD = 1
) t
group by GroupingField
order by GroupingField

I also used CTE expression to make coding easier and understandable

Output is as

enter image description here