Prakazz Prakazz - 1 year ago 65
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.


Answer Source

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

;with cte as (
        t.Label, t.ProcessedDate,
        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 (
     FirstProcessed = CASE when minD = 1 then Label else null end,
     LastProcessed = CASE when maxD = 1 then Label else null end
from cte 
    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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download