How make order random and distinct in sql query

Question

I need to select random value from table and it should be distinct . I tried but I could not please help this the query I have used but I got error say order by item must appear in the select list if select distinct is specified string checkentrycmd = select distinct TOP(10) * from dbo.Quiz where MaID= + Request.QueryString[ Id ].ToString() +  ORDER BY CHECKSUM(NEWID()) ;

Tapan Patel · Answer

You cannot do that because Distinct expects you to include the items mentioned in the Order by clause. But there is way to do that. I believe you cannot do that with Select distinct *. But you can achieve distinct on any column and and instead of doing distinct use group by that column. It will work. SELECT TOP (10) COL FROM TABLE GROUP BY COL ORDER BY NEWID() Please mark it as answer if addresses the issue.

Joma Rajab · Answer

Thanks Midhun I have figured out best regards

Joma Rajab · Answer

Thanks Midhun I did ti but I got incorrect syntax near *

Midhun T P · Answer

Hi Your query should be like below - select distinct TOP(10) CHECKSUM(NEWID()) as myorderid* from dbo.Quiz where MaID= + Request.QueryString[Id].ToString() +  ORDER BY myorderid