开发者

Stored procedure in SQL Server (distinct)?

I write a stored procedure in SQL Server like this

select distinct top 5 videos.videoid,videos.videotitle,videos.videoname,
   convert(varchar,videos.posteddate开发者_如何学Python,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.


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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜