Need Correction in 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

Need Correction in query

Author

Topic

Moo
Starting Member

10 Posts

Posted - 2008-07-25 : 01:57:10

Hi All,

i had tried this query but i was getting not the exact result

select top 5 e.uncid ,e.uncname ,e.description ,e.starttime,e.endtime ,u.username ,r.ranking ,
count(c.unccommentsid)as noofcomments
from A e
join B u on e.userid=u.userid
join C r on e.uncid=r.uncid
join D c on e.uncid=c.uncid

where e.deleted=0 group by e.uncid,e.uncname,e.description,e.starttime,e.endtime,u.username,
c.unccommentsid,r.ranking order by r.ranking desc

Here i was getting top 1 answer for all the top 5 answers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-25 : 01:59:53

[code]
select top 5
e.uncid ,
e.uncname ,
e.description ,
e.starttime ,
e.endtime ,
u.username ,
r.ranking ,
count(c.unccommentsid)as noofcomments
from A e
join B u on e.userid=u.userid
join C r on e.uncid=r.uncid
join D c on e.uncid=c.uncid
where e.deleted=0
group by e.uncid,
e.uncname,
e.description,
e.starttime,
e.endtime,
u.username,
~~c.unccommentsid,~~
r.ranking
order by r.ranking desc
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-25 : 02:05:16

OR this way

SELECT 	TOP 5 
       	e.uncid ,
       	e.uncname ,
       	e.description ,
       	e.starttime ,
       	e.endtime ,
       	u.username ,
       	r.ranking ,
       	c.noofcomments
FROM   	A e
	JOIN B u ON e.userid=u.userid
	JOIN C r ON e.uncid=r.uncid
	JOIN
	(
		SELECT	uncid, COUNT(c.unccommentsid) AS noofcomments
		FROM	D
		GROUP BY uncid
	) c 	ON e.uncid=c.uncid
WHERE 	e.deleted = 0 
ORDER BY r.ranking DESC

KH
[spoiler]Time is always against us[/spoiler]

Moo
Starting Member

10 Posts

Posted - 2008-07-25 : 02:06:39

K.Thank you .I got it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources