Friday 10 October 2014

SQL - Get First Value of Every Category | Using While Loop

create table #temp
(id int,
monthDate date,
value int)

insert into #temp values(1,'1/1/2014',10)
insert into #temp values(1,'1/2/2014',15)
insert into #temp values(1,'1/3/2014',20)
insert into #temp values(2,'1/4/2014',25)
insert into #temp values(2,'1/5/2014',19)

declare @min int,@max int
select @min=MIN(ID) from #temp
select @max=MAX(ID) from #temp

select * from #temp

select top 0 * into  #res 
from #temp

while(@min<=@max)
begin

declare @minDT date
set @minDT=(select MIN(MonthDate) from #temp where id=@min)

insert into #res
select *
from #temp
where ID=@min
and Convert(Date,monthDate,103)=Convert(Date,@minDT,103)

set @min=@min+1
end

select * from #res

drop table #res
drop table #temp

Result:

No comments:

Post a Comment