Question 113450 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to find the shortest song name in each music album

I use mysql and I have two tables with many to many relation and third table contains their foreign keys:

album table:
id  name   

composition table: 
id name duration

links table:
id  album_id    composition_id

How can I create sql query to get the shortest song name in each album by duration?
I tried to make somethink like:

SELECT al.name, c.name FROM album al
JOIN links l ON l.album_id = al.id
JOIN composition c ON l.composition_id = c.id
GROUP BY al.name
HAVING //don't know what next
Tags:

2

Answers


  1. 0

    If you use PostgreSQL:

    SELECT DISTINCT ON (al.name) al.name, c.name FROM album al
JOIN links l ON l.album_id = al.id
JOIN composition c ON l.composition_id = c.id
ORDER BY al.name, c.duration

    https://www.geekytidbits.com/postgres-distinct-on/

    Login or Signup to reply.
  2. 0 
    SELECT album_name, composition_name, duration
FROM (
  SELECT al.name AS album_name, 
    c.name AS composition_name,
    c.duration
    ROW_NUMBER() OVER (PARTITION BY al.id ORDER BY c.duration) AS rownum
  FROM album al
  JOIN links l ON l.album_id = al.id
  JOIN composition c ON l.composition_id = c.id
) AS t
WHERE rownum = 1;

    Window functions require MySQL 8.0.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search