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.
精彩评论