Retrieving Random Rows from Table Using NEWID()

Question

i have a sql query and i am using distinct statement

   CREATE proc SProc_SelectPhotographersByLocation         
@locationID varchar(500)          
as            
begin          


DECLARE @TEST varchar(1000)          
DECLARE @SQLQuery AS NVARCHAR(1000)          

SET @TEST = @locationID          

SET @SQLQuery = 'select distinct ContributerSubCategoryMapping.ContributorID,  PhotographerContributors_tbl.*  from ContributerSubCategoryMapping               
  left outer join PhotographerContributors_tbl on PhotographerContributors_tbl.ContributorId=ContributerSubCategoryMapping.ContributorID              
  left outer join tbl_City on tbl_City.CityID=PhotographerContributors_tbl.Location              
  where         
  PhotographerContributors_tbl.Location IN('+ @locationID +') and PhotographerContributors_tbl.IsActive=''1'' order by Newid()'          
EXECUTE(@SQLQuery)          

end

i am getting error on the query when i use NEWID() on that query. Error is

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

please help me for this issue

Answer 1

Use group by instead of distinct. One way is by listing the columns explicitly:

select csm.ContributorID, pc.col1, pc.col2, . . .
from ContributerSubCategoryMapping csm left outer join
     PhotographerContributors_tbl pc
     on pc.ContributorId = csm.ContributorID left outer join
     tbl_City c
     on c.CityID = pc.Location              
where pc.Location IN ('+ @locationID +') and pc.IsActive=''1''
group by csm.ConstributorId, pc.col1, pc.col2, . . .
order by Newid();

However, I don't understand the query. The tables ContributerSubCategoryMapping and tbl_City don't seem to be being used. So why not just do this?

select pc.*
from PhotographerContributors_tbl pc
where pc.Location IN ('+ @locationID +') and pc.IsActive=''1''
order by Newid();