SQL Query to return n random content items in Orchard Core

Question

In my Orchard Core application I want to add an SQL query to get 3 random products and the query I use is as below:

SELECT TOP (3) * FROM ContentItemIndex WHERE (ContentType = 'Product') AND (Published = 1) ORDER BY newid()

This query works when I run it on SQL Server on the application database. But when I run it in Orchard Core environment it gives this error:

Syntax error, expected: OVER, AS, id_simple, ,, HAVING, ORDER, LIMIT, OFFSET, ), UNION, ;, SELECT, FROM, WHERE, GROUP at line:0, col:15

Answer 1

Replace TOP(3) with LIMIT 3 in Template input:

SELECT * FROM ContentItemIndex WHERE (ContentType = 'Product') AND (Published = 1) ORDER BY newid() LIMIT 3;

And Final SQL Query will be:

SELECT TOP (3) * FROM ContentItemIndex WHERE (ContentType = 'Product') AND (Published = 1) ORDER BY newid()