Getting the minimum of column on the basis of other field

Question

I have a scenario wherein I have

Id|rank|   date
1 | 7  |07/08/2015
1 | 7  |09/08/2015
1 | 8  |16/08/2015
1 | 8  |17/08/2015
1 | 7  |19/08/2015
1 | 7  |15/08/2015
2 | 7  |01/08/2015
2 | 7  |02/08/2015
2 | 8  |16/08/2015
2 | 8  |17/08/2015
2 | 7  |26/08/2015
2 | 7  |28/08/2015

My desired solution is

1 | 7  |07/08/2015
1 | 8  |16/08/2015
1 | 7  |15/08/2015
2 | 7  |01/08/2015
2 | 8  |16/08/2015
2 | 7  |26/08/2015

i.e for each block of id and rank I want the minimum of date. I have tried using while loop as there are thousands of records it is taking 2 hours to load.Is there any other way to do please suggest.

Search for something like "sql first row in group" Here's one: http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group — shawnt00, Aug 18 '16 at 16:46
why is 1,7,15/08/2015 repeated twice once for 07/08 and 15/08 — TheGameiswar, Aug 18 '16 at 16:48
No it is not repeated ...1 and 7 are repeated as I want output for particular batch of id and rank — user2941762, Aug 18 '16 at 16:51

score 1 · Answer 1 · answered Aug 18 '16 at 19:28

For each row give unique row number using necessary order. (As I get Id is more important than date and date is more important than rank).

Join resulting table to itself using row numbers shifted by one row (d1.RowNum = d2.RowNum+1).

Select only rows that are joined to "other block" rows (d1.Id <> d2.Id or d1.Rank <> d2.rank).

Depending on shifting direction and selected table either maximal or minimal date will be selected.

Don't forget "edge case" - row that due to shifting can't be joined (that's why not inner join and d1.RowNum = 1 condition used).

;WITH dataWithRowNums as (
    select Id, Rank, Date,
        RowNum = ROW_NUMBER() OVER (ORDER BY Id,date,rank) 
    from YourTable
)
select d1.Id, d1.Rank, d1.Date
from dataWithRowNums d1
left join dataWithRowNums d2 
    on d1.RowNum = d2.RowNum+1 and (d1.Id <> d2.Id or d1.Rank <> d2.rank)
where not d2.Id is null or d1.RowNum = 1

This code returns result bit different from yours:

Id  Rank    Date
1   7   2015-08-07
1   8   2015-08-16
1   7   2015-08-19 <-- you've got here 2015-08-15 
2   7   2015-08-01
2   8   2015-08-16
2   7   2015-08-26

As block (Rank 8 Id 1) have started at 16/08 so row 15/08 for rank 7 is related to first block (rank7 Id1).

If you still need your sorting (so 15/08 rank 7 is related to second block (rank7 id1)) then you should provide your own RowSorting data and then ask here about another solution for another task )

score 0 · Answer 2 · answered Aug 18 '16 at 17:36

0

Here is the query using row_number()

 ;WITH cte_rec
  as (SELECT Id,Rank,Date
                    ,ROW_NUMBER()OVER (partition by Id,Rank ORDER BY date) as RNO
        FROM YourTable)
       SELECT Id,Rank,Date
       FROM cte_rec
       WHERE RNO =1

answered Aug 18 '16 at 17:36

Unnikrishnan R

4,965
1
12
21

Thanks unni but this will not achieve the desire result – user2941762 Aug 18 '16 at 18:25

score 0 · Answer 3 · answered Aug 22 '16 at 12:20

This is what I have tried and is running as expected
create table #temp
    (
           iden int identity(1,1),
           ID int,
           [rank] int,
           [date] date,
           dr_id int,
           rownum_id int,
           grouprecord int
    )


    Insert into #temp(id,rank,date)
    select 1 , 7  ,'07/08/2015'
    union all select  1 , 7  ,'09/08/2015'
    union all select  1 , 8  ,'08/16/2015'
    union all select  1 , 8  ,'08/17/2015'
    union all select  1 , 7  ,'08/19/2015'
    union all select  1 , 7  ,'08/15/2015'
    union all select  2 , 7  ,'08/01/2015'
    union all select  2 , 7  ,'08/02/2015'
    union all select  2 , 8  ,'08/16/2015'
    union all select  2 , 8  ,'08/17/2015'
    union all select  2 , 7  ,'08/26/2015'
    union all select  2 , 7  ,'08/28/2015'


    update t1
    set dr_id = t2.rn
    from #temp t1 inner join
    (select  iden, dense_rank() over(order by id) as rn from #temp) t2
    on t1.iden = t2.iden


    update t1
    set rownum_id = t2.rn
    from #temp t1 inner join
    (select  iden, row_number() over(partition by dr_id order by id) as rn from #temp) t2
    on t1.iden = t2.iden




    select *,row_number() over(order by iden)rn into #temp1 from
    (             
                         select t2.* 
                  from #temp t1 inner join #temp t2
                  on (t1.dr_id = t2.dr_id or t2.dr_id = (t1.dr_id +1) ) and ( t1.rank<>t2.rank or t2.dr_id = (t1.dr_id +1) )
                  and t2.iden = t1.iden  + 1
    )a





    declare @id int,@miniden int,@maxiden int,@maxid int
    set @id = 1
    select @maxid = max(iden) from #temp

    while exists(select 1 from #temp1 where rn = @id)
    begin
           Select @miniden = iden from #temp1
           where rn = @id

           Select @maxiden = iden from #temp1
           where rn = @id+1

           update #temp
           set grouprecord = @id +1
           where iden between @miniden and @maxiden

    IF(@maxiden IS NULL)
    BEGIN 
           Update #temp
           set grouprecord = @id +1
           where iden between @miniden and @maxid
    END

           set @id = @id + 1
           SET @miniden =NULL
           SET @maxiden = NULL
    end

    UPDATE #TEMP
    SET GROUPRECORD = 1
    WHERE GROUPRECORD IS NULL



    select min(date) as mindate,grouprecord from #temp
    group by grouprecord

Thanks everyone the help :)

Getting the minimum of column on the basis of other field

3 Answers3