How to Order by ? - 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

How to Order by ?

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-20 : 02:36:12

Hi Friends,
I used the below query to display the disscussions order by creationdatetime of disscussion Table.

select m.firstname,d.CreationDateTime,d.DissId,d.groupId,
d.Userid,d.disstitle,d.dissdetails
,(select count(Dissid) from comments where d.dissid=dissid and
deleted=0 and status=1 ) as NoOfComments
from Disscussions d
join member m on d.userid=m.userid

where d.groupid= 5 and d.deleted=0 and d.Status=1
order by d.creationdatetime desc

Now what i need is,i had one more table named comments.Comments table contains Comments for the disscuisions table.I need top 10 disscussions order by creationdatetime of Comments .

Would you please anyone help on this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 02:43:42

but wont you have more than one comments record for a discussion? in such you want all records to be returned? it will cause discussion info to be repeated for each.

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-20 : 02:58:26

More than one Comment can inserted for Disscussions,but when i am displaying i need to display only one disscussion order by creationdatetime of comments

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-20 : 04:08:06

quote:
Originally posted by Kotti

More than one Comment can inserted for Disscussions,but when i am displaying i need to display only one disscussion order by creationdatetime of comments

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-20 : 05:19:04

Both Disscusions and Comments table are sample
Disscussions Table has Dissid,DissDetails,CreationDateTime
COmments Table Has CommId,Dissid,CommDetails,CreationDateTime

DissId DissDetails CreationDateTime
1 Test1 2008-10-16 07:44:11.443
2 Test2 2008-10-17 07:44:11.443
3 Test3 2008-10-18 07:44:11.443

CommId Dissid CommDetails CreationDateTime
1 2 Sample1 2008-10-18 08:44:11.443
2 3 Sample2 2008-10-19 07:44:11.443
3 2 Sample3 2008-10-19 07:44:11.443
4 1 sample4 2008-10-20 07:44:11.443
5 3 sample5 2008-10-21 07:44:11.443

I need to display the Disscussions table details order by the creationdateTime of comments table .Details should not be repeated

EXPECTED OUTPUT:
DissId DissDetails CreationDateTime
3 Test3 2008-10-18 07:44:11.443
1 Test1 2008-10-16 07:44:11.443
2 Test2 2008-10-17 07:44:11.443

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 05:26:14

[code]SELECT d.Dissid,d.DissDetails,d.CreationDateTime
FROM Disscusions d
INNER JOIN (SELECT Dissid, MAX(CreationDateTime) AS LatestComment
FROM Comments
GROUP BY Dissid)c
ON c.Dissid=d.Dissid
ORDER BY c.LatestComment DESC[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-20 : 09:14:51

quote:

Originally posted by visakh16

SELECT d.Dissid,d.DissDetails,d.CreationDateTime
FROM Disscusions d
INNER JOIN (SELECT Dissid, MAX(CreationDateTime) AS LatestComment
            FROM Comments
            GROUP BY Dissid)c
ON c.Dissid=d.Dissid
ORDER BY c.LatestComment DESC


SELECT d.Dissid,d.DissDetails,d.CreationDateTime
FROM Disscusions d
INNER JOIN (SELECT Dissid, MAX(CreationDateTime) AS LatestComment
            FROM Comments
            GROUP BY Dissid)c
ON c.Dissid=d.Dissid AND
c.LatestComment=d.CreationDateTime 
ORDER BY c.LatestComment DESC

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources