Stored Procedure In Sql Server (order By Desc)?

I have a stored procedure that will give the latest records i.e., order by added date this is my procedure.... select distinct top 5 videos.videoid,videos.videotitle,videos.video

Solution 1:

Use ORDER BY videos.posteddate

selectdistinct top 5
     videos.videoid,
     videos.videotitle,
     videos.videoname,
     convert(varchar,videos.posteddate,106) as  posteddate,
     videos.approvedstatus,
     videos.videoimage,
     (ISNULL(videos.views,0.0)) as [views],
     videos.privacy,
     (isnull(videos.rating,0.0)) as rating,
     videos.userid,
     users.userid,users.username
  from
     videos
     leftouterjoin
     users on videos.userid=users.userid
  where
     videos.approvedstatus='Y'and videos.privacy='P'orderby
     videos.posteddate desc

Your original is the equivalant of ORDER BY convert(varchar,videos.posteddate,106) DESC

So you are sorting by "dd mon yyyy" string, not the desired actual datetime (yyyy-mm-dd hh etc)

I guess this is SQL Server 2000: from memory SQL Server 2005 will not accept this ambiguity