SteemSQL Tutorial: How to Get Random Posts on SteemIt?

MSSQL Database

We have seen many usages of returning some random records from MSSQL using STEEMSQL for example, pick some random posts published today in history (last year, the year before last …), we can write a SQL like this:

SELECT TOP 10 
    author, body
FROM
    Comments (NOLOCK)
WHERE
    FORMAT(created,'MM-dd','en-us') = FORMAT(GETUTCDATE(),'MM-dd','en-us')
    AND YEAR(created) <> YEAR(GETUTCDATE())
    AND depth = 0
ORDER BY
    NEWID()

SELECT TOP 10 
    author, body
FROM
    Comments (NOLOCK)
WHERE
    FORMAT(created,'MM-dd','en-us') = FORMAT(GETUTCDATE(),'MM-dd','en-us')
    AND YEAR(created) <> YEAR(GETUTCDATE())
    AND depth = 0
ORDER BY
    NEWID()

Notes:

only selecting the fields you want, which is faster than returning all fields select *
Put a (NOLOCK) to avoid database being injected lock
USE GetUTCDate() function as all dates & times in STEEMSQL are UTC.
Use depth = 0 to limit to posts only while depth > 0 refers to comments
ORDER BY NEWID() chooses random records, but this is slow as it needs to scan entire table and sort by NEWID()

We can also add:

1	category = "maintag"

category = "maintag"

to limit to specific categories. Better way to return random records: We can add the following condition and remove the ORDER BY NEWID() which is inefficient.

1	AND (ABS(CAST( (BINARY_CHECKSUM(ID, NEWID())) as int)) % 100) < 50

AND (ABS(CAST( (BINARY_CHECKSUM(ID, NEWID())) as int)) % 100) < 50

The BINARY_CHECKSUM function is fast and there is no need to scan entire table and do the sorting afterwards. Of course, there are other ways to return random records, e.g. generate a set of random IDs in the scripting (like Python, PHP) and then pass these IDs in the SQL.

You may also like: STEEM SQL 系列之随机返回是怎么实现的？

Do you have other better ways? please share yours by commenting below. Innovative (better) solutions will be rewarded with 1 SBD.