CODEWITHSUNDEEP
sqlsql-serverrank
Prakash
Prakash

Reputation: 555

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

Upvotes: 1

Views: 174

Answers (2)

Mansoor
Mansoor

Reputation: 4192

    CREATE TABLE #Details (ID INT,LabelID INT,GroupingField INT)
CREATE TABLE #Details1 (ID INT,Label VARCHAR(100),ProcessedDate VARCHAR(100)) 

INSERT INTO #Details1 (ID ,Label ,ProcessedDate )
SELECT 1,'Label1','11/01/2016' UNION ALL
SELECT 2,'Label2','11/02/2016' UNION ALL
SELECT 3,'Label3','11/03/2016' UNION ALL
SELECT 4,'Label4','11/04/2016' UNION ALL
SELECT 5,'Label5','11/05/2016'


INSERT INTO #Details (ID ,LabelID ,GroupingField )
SELECT 1,1,101 UNION ALL
SELECT 2,2,101 UNION ALL
SELECT 3,3,101 UNION ALL
SELECT 4,4,101 UNION ALL
SELECT 5,5,101 UNION ALL
SELECT 6,1,102 UNION ALL 
SELECT 7,2,102 UNION ALL
SELECT 8,3,102 UNION ALL
SELECT 9,4,102

;WITH CTE (GroupingField , MAXId ,MinId) AS
(
    SELECT GroupingField,MAX(LabelID) MAXId,MIN(LabelID) MinId
    FROM #Details
    GROUP BY GroupingField  
)


SELECT GroupingField ,B.Label FirstProcessed, A.Label LastProcessed
FROM CTE
JOIN #Details1 A ON MAXId = A.ID
JOIN #Details1 B ON MinId = B.ID

Upvotes: 3

Eralper
Eralper

Reputation: 6612

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

Upvotes: 1

Related Questions