select query - 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

General SQL Server Forums

New to SQL Server Programming

select query

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2008-10-24 : 09:51:48

Hi Friends

I have two table Request and Reply

Tbl_Request

ReqId
Topic
Description
Createdate
Modifydate

Tbl_Reply

RepId
ReqId
Topic
Description
Createdate
Modifydate

Result

3 requests with most Reply in descending order of number of Reply

Thanks and Regards
E.sambath kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 09:57:02

[code]SELECT TOP 3 r.*,rp.RepCount
FROM Tbl_Request r
INNER JOIN (SELECT ReqId,COUNT(RepId) AS RepCount
FROM Tbl_Reply
GROUP BY ReqId)rp
ON rp.ReqId=r.ReqId
ORDER BY rp.RepCount DESC[/code]

esambath
Yak Posting Veteran

89 Posts

Posted - 2008-10-24 : 10:05:40

quote:

Originally posted by visakh16

SELECT TOP 3 r.*,rp.RepCount
FROM Tbl_Request r
INNER JOIN (SELECT ReqId,COUNT(RepId) AS RepCount
            FROM Tbl_Reply
            GROUP BY ReqId)rp
ON rp.ReqId=r.ReqId
ORDER BY rp.RepCount DESC

Hi visakh16

Thank you I got the result.

Thanks and Regards
E.sambath kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 10:06:50

quote:
Originally posted by esambath

quote:
Originally posted by visakh16

SELECT TOP 3 r.*,rp.RepCount
FROM Tbl_Request r
INNER JOIN (SELECT ReqId,COUNT(RepId) AS RepCount
FROM Tbl_Reply
GROUP BY ReqId)rp
ON rp.ReqId=r.ReqId
ORDER BY rp.RepCount DESC

Hi visakh16

Thank you I got the result.

Thanks and Regards
E.sambath kumar

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources