Our Training Programs

All contents are copyright of their authors.

How make order random and distinct in sql query

How make order random and distinct in sql query

Joma Rajab

10yAsked Sep 1, 2016, 11:03 AM2.3k.NET

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

Know the answer? Post it — somebody with the same question will find it here.

It is the same account you read, post and publish with — and you will come straight back to this page.

Tapan Patel10y ago
Accepted 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 Rajab10y ago
Thanks Midhun
I have figured out
best regards
Joma Rajab10y ago
Thanks Midhun
I did ti but I got incorrect syntax near *
Midhun Tp10y ago
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