Stored procedure in SQL Server (distinct)?

Question

I write a stored procedure in SQL Server like this

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

but i am getting this error can u suggest me

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Answer 1

Instead of Video.posteddate use posteddate directly.

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

Answer 2

when you are using distinct, the unique rows are picked according to your select statement. Later in query when you use order by columns, the columns are not in result set so you need to add those missing columns in your select list too.