Difference between revisions of "User:Taweiss"
Jump to navigation
Jump to search
Line 1: | Line 1: | ||
Active when I have time. Should be at least weekly. | Active when I have time. Should be at least weekly. | ||
+ | Trying different SQL queries. Seeing if I can find the most reprinted short story. | ||
+ | |||
+ | SELECT | ||
+ | (SELECT | ||
+ | GROUP_CONCAT(CONCAT(a.author_lastname, | ||
+ | ', ', | ||
+ | TRIM(SUBSTRING_INDEX(a.author_canonical, a.author_lastname, 1))) | ||
+ | SEPARATOR '|') | ||
+ | FROM | ||
+ | authors a, | ||
+ | canonical_author pa | ||
+ | WHERE | ||
+ | t.title_id = pa.title_id | ||
+ | AND a.author_id = pa.author_id) author_details, | ||
+ | t.title_title, | ||
+ | pubs.pub_title, pubs.pub_isbn, | ||
+ | COUNT(t.title_title) AS dupe_cnt | ||
+ | FROM | ||
+ | titles t | ||
+ | INNER JOIN | ||
+ | pub_content pc ON t.title_id = pc.title_id | ||
+ | INNER JOIN | ||
+ | pubs ON pubs.pub_id = pc.pub_id | ||
+ | WHERE | ||
+ | t.title_title BETWEEN 'A' AND 'z' | ||
+ | AND t.title_ttype = 'SHORTFICTION' | ||
+ | GROUP BY author_details , t.title_title | ||
+ | HAVING COUNT(t.title_title) > 50 ; |
Revision as of 02:24, 7 March 2020
Active when I have time. Should be at least weekly. Trying different SQL queries. Seeing if I can find the most reprinted short story.
SELECT
(SELECT GROUP_CONCAT(CONCAT(a.author_lastname, ', ', TRIM(SUBSTRING_INDEX(a.author_canonical, a.author_lastname, 1))) SEPARATOR '|') FROM authors a, canonical_author pa WHERE t.title_id = pa.title_id AND a.author_id = pa.author_id) author_details, t.title_title, pubs.pub_title, pubs.pub_isbn, COUNT(t.title_title) AS dupe_cnt
FROM
titles t INNER JOIN pub_content pc ON t.title_id = pc.title_id INNER JOIN pubs ON pubs.pub_id = pc.pub_id
WHERE
t.title_title BETWEEN 'A' AND 'z' AND t.title_ttype = 'SHORTFICTION'
GROUP BY author_details , t.title_title HAVING COUNT(t.title_title) > 50 ;