User:Taweiss

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 ;