sqlite
unknown
sql
a year ago
4.8 kB
5
Indexable
Never
select release.name, min(release_info.date_year) from (release inner join release_info on release.id = release_info.release) inner join artist_credit on release.artist_credit = artist_credit.id inner join medium on medium.release = release.id inner join artist_credit_name on artist_credit_name.artist_credit = artist_credit.id inner join artist on artist_credit_name.artist = artist.id where artist.name = "The Beatles" and medium.format = 31 and release_info.date_year <= artist.end_date_year and release_info.area = 221 group by release.name order by release_info.date_year, release.name; SELECT release.name, MIN(release_info.date_year) FROM release JOIN release_info ON release.id = release_info.release JOIN artist_credit ON release.artist_credit = artist_credit.id JOIN medium ON medium.release = release.id JOIN artist_credit_name ON artist_credit_name.artist_credit = artist_credit.id JOIN artist ON artist_credit_name.artist = artist.id WHERE artist.name = 'The Beatles' AND medium.format = 31 AND release_info.date_year <= artist.end_date_year AND release_info.area = 221 GROUP BY release.name ORDER BY MIN(release_info.date_year), release.name; select release.name, artist_credit.name, release_info.date_year from release inner join release_info on release.id = release_info.release inner join artist_credit on release.artist_credit = artist_credit.id inner join medium on release.id = medium.release inner join medium_format on medium.format = medium_format.id where medium_format.name = "Cassette" order by release_info.date_year desc, release_info.date_month desc, release_info.date_day desc, release.name asc, artist_credit.name asc limit 10; SELECT release.name, artist_credit.name, release_info.date_year FROM release JOIN release_info ON release.id = release_info.release JOIN artist_credit ON release.artist_credit = artist_credit.id JOIN medium ON release.id = medium.release JOIN medium_format ON medium.format = medium_format.id WHERE medium_format.name = 'Cassette' ORDER BY release_info.date_year DESC, release_info.date_month DESC, release_info.date_day DESC, release.name ASC, artist_credit.name ASC LIMIT 10; WITH NEWTABLE1 AS ( SELECT work_type.name AS WORK_TYPE, work.name AS WORK_NAME, LENGTH(work.comment) AS COMMENT_LENGTH, work.comment AS COMMENT FROM work INNER JOIN work_type ON work.type = work_type.id WHERE LENGTH(work.comment) > 0 ), NEWTABLE2 AS ( SELECT WORK_TYPE, WORK_NAME, COMMENT_LENGTH, COMMENT, ROW_NUMBER() OVER (PARTITION BY WORK_TYPE ORDER BY COMMENT_LENGTH DESC, WORK_NAME ASC) AS rn FROM NEWTABLE1 ), NEWTABLE3 AS ( SELECT WORK_TYPE, WORK_NAME, COMMENT_LENGTH, COMMENT FROM NEWTABLE2 WHERE rn <= 2 ORDER BY WORK_TYPE ASC, WORK_NAME ASC ), NEWTABLE4 AS ( SELECT WORK_TYPE, MAX(COMMENT_LENGTH) AS MAX_LENGTH FROM NEWTABLE2 GROUP BY WORK_TYPE ) SELECT NEWTABLE3.WORK_TYPE, WORK_NAME, COMMENT_LENGTH, COMMENT FROM NEWTABLE3 INNER JOIN NEWTABLE4 WHERE NEWTABLE3.WORK_TYPE = NEWTABLE4.WORK_TYPE AND NEWTABLE3.COMMENT_LENGTH = NEWTABLE4.MAX_LENGTH; WITH NEWTABLE1 AS ( SELECT work_type.name AS WORK_TYPE, work.name AS WORK_NAME, LENGTH(work.comment) AS COMMENT_LENGTH, work.comment AS COMMENT FROM work INNER JOIN work_type ON work.type = work_type.id WHERE LENGTH(work.comment) > 0 ), NEWTABLE2 AS ( SELECT WORK_TYPE, WORK_NAME, COMMENT_LENGTH, COMMENT, ROW_NUMBER() OVER (PARTITION BY WORK_TYPE ORDER BY COMMENT_LENGTH DESC, WORK_NAME ASC) AS rn FROM NEWTABLE1 ), NEWTABLE3 AS ( SELECT WORK_TYPE, WORK_NAME, COMMENT_LENGTH, COMMENT FROM NEWTABLE2 WHERE rn <= 2 ORDER BY WORK_TYPE ASC, WORK_NAME ASC ), NEWTABLE4 AS ( SELECT WORK_TYPE, MAX(COMMENT_LENGTH) AS MAX_LENGTH FROM NEWTABLE2 GROUP BY WORK_TYPE ) SELECT NT3.WORK_TYPE, NT3.WORK_NAME, NT3.COMMENT_LENGTH, NT3.COMMENT FROM NEWTABLE3 AS NT3 INNER JOIN NEWTABLE4 AS NT4 ON NT3.WORK_TYPE = NT4.WORK_TYPE AND NT3.COMMENT_LENGTH = NT4.MAX_LENGTH ORDER BY NT3.WORK_TYPE, NT3.WORK_NAME; WITH TEMP AS ( SELECT DISTINCT release.id AS RELEASE_ID, release.artist_credit AS RELEASE_ARTIST_CREDIT, release_info.date_month AS DATE_MONTH FROM release INNER JOIN release_info ON release.id = release_info.release ), NEWTABLE1 AS ( SELECT artist.name AS ARTIST_NAME, TEMP.DATE_MONTH AS RELEASE_MONTH, count(*) AS COUNT_NUMBER FROM TEMP INNER JOIN artist_credit ON TEMP.RELEASE_ARTIST_CREDIT = artist_credit.id INNER JOIN artist_credit_name ON artist_credit.id = artist_credit_name.artist_credit INNER JOIN artist ON artist_credit_name.artist = artist.id WHERE artist.type = 1 AND TEMP.DATE_MONTH is not null AND artist.name like 'Elvis%' GROUP BY ARTIST_NAME, RELEASE_MONTH ) SELECT ARTIST_NAME, RELEASE_MONTH, MAX(COUNT_NUMBER) FROM NEWTABLE1 GROUP BY ARTIST_NAME ORDER BY COUNT_NUMBER DESC, ARTIST_NAME ASC;