Need help to wrote a complex query

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

General SQL Server Forums

New to SQL Server Programming

Need help to wrote a complex query

Author

Topic

anujpratap84
Starting Member

45 Posts

Posted - 2011-03-18 : 07:06:33

Hi All,

I stuck in a query.
Here is my data, i want to get the msgId (5,11 both) which have minimun modified date.
Expected output: row of LogId 3 and 4.

LogId msgId masterID Message ModifiedOn
1 5 1 You have won {1}.Updated 2011-03-17 23:04:12
2 11 1 You have won {1}.aaaa 2011-03-17 23:04:15
3 11 1 You have won {1}.Frenach 2011-03-17 20:04:12
4 5 1 You have won {1}.Updated 2011-03-17 21:08:12
5 11 1 You have won {1}.Frenach 2011-03-17 21:10:12

Anyone have any idea.
Thanks in advance.

Anuj Pratap Singh

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-18 : 07:18:55

select *

from @table t1
inner join
( select msgid,min(ModifiedOn) as mDate
from yourTable
group by msgid) t2
on
t1.msgid =t2.msgid

and t1.modifiedon = t2.mdate

Jim

Everyday I learn something that somebody else already knew

anujpratap84
Starting Member

45 Posts

Posted - 2011-03-18 : 07:41:25

Thanks a lot Jim.
It's working like charm...

quote:
Originally posted by jimf

select *

from @table t1
inner join
( select msgid,min(ModifiedOn) as mDate
from yourTable
group by msgid) t2
on
t1.msgid =t2.msgid

and t1.modifiedon = t2.mdate

Jim

Everyday I learn something that somebody else already knew

Anuj Pratap Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources