Luke,

IMHO ?

Kent,

select

Code,

( select top 1 status

from @emp b

where a.Code = b.Code

order by MID

...

Ok.I will try it out.

I am using sql2000.

Can you give me the full query ?

I don't want to use any tables ( including spt_values or Tally table). How to acheive it ?

Karthik, if you want to know how it works, I have an article coming out on May 7th (just 4 more days... :hehe called "The "Numbers" or "Tally" Table: What...

Ok.

Basically, what i want to display is

I want to display the count which is having '0' records in Dept table.

so i have written the query like

select A,Eno,Datepart(Month,JoinDate),Datepart(Year,JoinDate),count(*)

from Emp1 A,Dept...

Sorry, i posted wrong query !

Query:

Update Emp

set CounterPart = count(*)

from Emp1 A,Dept B

where A.DeptNo = b.DeptNo

AND A.Eno = '101'

GROUP BY A.Eno,Datepart(Month,JoinDate),Datepart(Year,JoinDate)

Answers are welcome !

Ryan,

Thanks a lot.

I have modified your query little

select PRID, min(Year * 100 + Month)/100 as Year,

min(Year * 100 + Month)%100 as Month,

From #t1 group by PRID

It is working perfectly.

ygulas,

i have tried out the above method.

but it failed.

Ryan,

How the minimum Month value is added exactly with year ? Kindly explain me.

If i execute like

Select PRID,Min(Year),Min(Month)

from #t1

group by PRID

it gives us the wrong output.

AAA 2002 1 -- HERE...

Ryan,

Wow!

select PRID, min(Year * 100 + Month) as YearAndMonth from #t1 group by PRID

Can you explain me the above one ? It is different.

Jeff,

Really Great!

You taught me a new concept.

Thanks a lot for your prompt reply with good explanations...