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

1. This is SQL Server syntax, not MySQL, I think your tags are incorrect. 2. NEWID() does not appear in this query, you should post the query that actually causes the error. 3. The error message seems fairly self explanatory, you cannot use distinct, and order by NEWID() without also selecting NEWID(), which will make the distinct meaningless, instead move your query into a subquery with select distinct, then order this subquery by NEWID(). 4. Why are you even ordering by NEWID() — GarethD
– GarethD, Commented Sep 5, 2014 at 10:14
thanks for the reply. actually i am getting randomly data from this query if i am not using order by newid() it's executed perfectly.is there another way to select randomly data from this query please help — Gitz
– Gitz, Commented Sep 5, 2014 at 10:38
What should your query return? Why is the DISTINCT in there anyway? — gvee
– gvee, Commented Sep 5, 2014 at 10:48
Not within the scope of the question but I would really advise against this dynamic SQL approach. If you are using SQL Server 2008 or later use table valued parameters. — GarethD
– GarethD, Commented Sep 5, 2014 at 11:08

Gordon Linoff · Accepted Answer · 2014-09-05 11:00:15Z

5

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();

answered Sep 5, 2014 at 11:00

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Donal Over a year ago

I like this suggestion. I have reached my daily vote limit - otherwise I would have upvoted it.

GarethD Over a year ago

Yep, sums up all my thoughts, why use 2 joins when you can use none... Also to add to "One way is by listing the columns explicitly" - Should be doing this anyway

Collectives™ on Stack Overflow

Retrieving Random Rows from Table Using NEWID()

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related