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:
1 2 3 4 5 6 7 8 9 10 | 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.
STEEM SQL Tutorial
- SteemSQL Tutorial: How to Get Random Posts on SteemIt?
- SteemSQL Tutorial: How to Get Authors Order By Potential Payout in Last 7 days?
- SteemSQL Tutorial: How to Get Historic Posts of Today on SteemIt?
- SteemSQL Tutorial: How to Calculate Monthly Income on STEEMIT?
- SteemSQL Tutorial: Can we Really Recover Deleted Comments/Posts on STEEMIT?
- SteemSQL Tutorial: What are the Outgoing Votes for Big Whales?
- SteemSQL Tutorial: Count Total Interests Sent
- SteemSQL Tutorial: Finding Inactive Steemians that You Follow
- SteemSQL Tutorial: How to Fix “JSON text is not properly formated. Unexpected character ‘.’ is found at position 0.”?
- SteemSQL Tutorial: How to Get the Most Payout Authors in History?
- SteemSQL Tutorial: Get Most Single Payout Authors
- SteemSQL Tutorial: How to Avoid SQL Injection?
- SteemSQL Tutorial: I have spent 800 SBD (7000+ USD) buying votes!
- SteemSQL Tutorial: How to Check If SteemSQL is Synchronized with Steem Blockchain?
–EOF (The Ultimate Computing & Technology Blog) —
Last Post: Monte Carlo solution for Mathematics × Programming Competition #7
Next Post: SteemSQL Tutorial - Can we Really Recover Deleted Comments/Posts on STEEMIT?