Retrieving random rows from a table using NEWID ()
I have a sql query and I am using the statement distinct
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 a request error when I use NEWID()
for this request. Mistake
ORDER BY items must appear in the select list if SELECT DISTINCT is specified .
please help me on this issue
+3
source to share
1 answer
Use group by
instead distinct
. One way is to explicitly list the columns:
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 request. Tables ContributerSubCategoryMapping
and tbl_City
doesn't seem to be used. So why not just do it?
select pc.*
from PhotographerContributors_tbl pc
where pc.Location IN ('+ @locationID +') and pc.IsActive=''1''
order by Newid();
+5
source to share