From 7188d0ee2c0e10415a86f2eac04e7a5dee82a7ad Mon Sep 17 00:00:00 2001 From: Bryan Newbold Date: Tue, 10 Mar 2020 22:43:15 -0700 Subject: DOI prefix example queries (SQL) --- sql/random_queries.md | 20 +++++++++++++++++--- 1 file changed, 17 insertions(+), 3 deletions(-) (limited to 'sql') diff --git a/sql/random_queries.md b/sql/random_queries.md index d88f45b..572b4f9 100644 --- a/sql/random_queries.md +++ b/sql/random_queries.md @@ -117,9 +117,23 @@ Or: Can also do some quick lookups for a specific domain and protocol like: SELECT * - FROM ingest_file_result - WHERE terminal_url LIKE 'https://insights.ovid.com/%' - LIMIT 10; + FROM ingest_file_result + WHERE terminal_url LIKE 'https://insights.ovid.com/%' + LIMIT 10; + +For a given DOI prefix: + + SELECT * + FROM ingest_file_result + WHERE base_url LIKE 'https://doi.org/10.17223/a%' + AND status = 'no-pdf-link' + LIMIT 10; + + SELECT status, count(*) + FROM ingest_file_result + WHERE base_url LIKE 'https://doi.org/10.17223/%' + GROUP BY status + ORDER BY count(*) DESC; ## Bulk Ingest -- cgit v1.2.3