Stored procedure in SQL Server (sorted in descending order)?

Question

I have a stored procedure that will give the latest records, that is, the order by the added date is my procedure ....

select distinct 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 left outer join users on videos.userid=users.userid where videos.approvedstatus='Y' and videos.privacy='P' order by posteddate desc 

but he does not give the last entries

when i execute the request

 select * from videos order by posteddate desc 

he gives accurate records. but the stored procedure does not give exact records. you can help me, thanks.

---

Answer 1

Use ORDER BY videos.posteddate

  select distinct 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 left outer join users on videos.userid=users.userid where videos.approvedstatus='Y' and videos.privacy='P' order by videos.posteddate desc 

Your original is equivalent to ORDER BY convert(varchar,videos.posteddate,106) DESC

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

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