Finding Minimum Value for ID - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Finding Minimum Value for ID

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-01-27 : 06:56:07

Hi Guys

I have a table similar to the following:
Week_Id, DayKey
200727, 20070702
200727, 20070703
200727, 20070704
200727, 20070705
200727, 20070706
200727, 20070707
200727, 20070708
200728, 20070709
200728, 20070710
200728, 20070711
200728, 20070712
200728, 20070713
200728, 20070714
200728, 20070715

I am trying to find the minimum DayKey value for each Week_Id , so the desired results would look like the following:

Week_Id, DayKey
200727, 20070702
200728, 20070709

Is this possible to do?

Thanking you in advance!

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-27 : 07:00:23

select week_id,min(daykey) from urtable group by week_id

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-27 : 07:01:03

select * from
( select *,row_number() over ( partition by Week_Id order by DayKey ) as rn from table) as t
where t.rn = 1

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-01-27 : 07:20:31

Hi

Thanks for that.

Sorry I forgot to mention that there other columns such as the day name, eg:
Week_Id, DayKey, DayName
200727, 20070702, Mon
200727, 20070703, Tue
200727, 20070704, Wed
200727, 20070705, Thur
200727, 20070706, Fri
200727, 20070707, Sat
200727, 20070708, Sun
200728, 20070709, Mon
200728, 20070710, Tue
200728, 20070711, Wed
200728, 20070712, Thur
200728, 20070713, Fri
200728, 20070714, Sat
200728, 20070715, Sun

The desired result would then be:

Week_Id, DayKey, DayName
200727, 20070702, Mon
200728, 20070709, Mon

Is this possible to do?

Thanks

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-27 : 07:25:48

try nageswar 9 solution

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-27 : 07:33:50

or

try this
declare @temp table ( Week_Id bigint, DayKey bigint,DayName varchar(40))
insert into @temp
select 200727, 20070702, 'Mon' union all
select 200727, 20070703, 'Tue' union all
select 200727, 20070704, 'Wed' union all
select 200727, 20070705, 'Thur' union all
select 200727, 20070706, 'Fri' union all
select 200727, 20070707, 'Sat' union all
select 200727, 20070708, 'Sun' union all
select 200728, 20070709, 'Mon' union all
select 200728, 20070710, 'Tue' union all
select 200728, 20070711, 'Wed' union all
select 200728, 20070712, 'Thur' union all
select 200728, 20070713, 'Fri' union all
select 200728, 20070714, 'Sat' union all
select 200728, 20070715, 'Sun'

select t1.week_id,t2.dk as Daykey,t1.dayname from @temp t1
inner join ( select week_id,min(daykey) as dk from @temp group by week_id ) t2 on t2.week_id = t1.week_id and t2.dk = t1.daykey

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-27 : 07:46:20

select b.* from
(select week_id,min(daykey) as mindate from urtable group by week_id) a
cross apply
(select week_id,daykey,dayname from urtable where week_id = a.week_id and daykey = a.mindate) b

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-27 : 08:06:02

[code]declare @temp table ( Week_Id int, DayKey int,dayname varchar(32))
insert into @temp
select 200727, 20070702, 'Mon' union all
select 200727, 20070703, 'Tue' union all
select 200727, 20070704, 'Wed' union all
select 200727, 20070705, 'Thur' union all
select 200727, 20070706, 'Fri' union all
select 200727, 20070707, 'Sat' union all
select 200727, 20070708, 'Sun' union all
select 200728, 20070709, 'Mon' union all
select 200728, 20070710, 'Tue' union all
select 200728, 20070711, 'Wed' union all
select 200728, 20070712, 'Thur' union all
select 200728, 20070713, 'Fri' union all
select 200728, 20070714, 'Sat' union all
select 200728, 20070715, 'Sun'

select Week_Id ,DayKey,DayNamefrom
( select *,row_number() over ( partition by Week_Id order by DayKey ) as rn from @temp) as t
where t.rn = 1 [/code]

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-01-27 : 08:47:08

Thanks Guys for all your help!!!

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-27 : 08:50:26

You are Welcome...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources