Finding the lowest value from list

Question

I have a table "songs" which is like this

Artist	Title	Pos	Date
artist1	title1	1	01-jan-1980
artist2	title0	9	01-jan-1980
artist3	title2	3	01-jan-1980
artist1	title1	1	07-jan-1980
artist2	title0	5	07-jan-1980
artist3	title2	4	07-jan-1980
artist1	title1	3	14-jan-1980
artist2	title0	6	14-jan-1980
artist3	title2	2	14-jan-1980

I'm trying group each Song (Title and Artist) by the lowest value of Pos so I would end up with

Artist	Title	Pos	Date
artist1	title1	1	01-jan-1980
artist2	title0	5	07-jan-1980
artist3	title2	2	14-jan-1980

It would be efficient if the select statment that gets each song "GROUP by Artist, Title" could also get the lowest Pos value.

I attempted to have a separate select statement that would find each lowest Pos value for each song from within the while loop that finds each song. E.g.

First select statement SELECT * FROM songs WHERE Date > $yearstart AND Date < $yearend Group BY Artist, Title ORDER BY Artist

Then from within the while loop SELECT Position, Date FROM singles WHERE Artist = '".$row['Artist']."' AND Title = '".$row['Title']."' ORDER BY Date";

This works fine (for about 600 songs) as the first select statement is in some cases checking 10,000 rows and this second statement checks all 10,000 again for the lowest Pos value for each song. Clearly that was going to be inefficient, and to the point MySQL aborted!

Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — Joe, Aug 26 '23 at 11:24

score 0 · Answer 1 · answered Aug 26 '23 at 11:21

Select s.Artist, s.Title, s.Pos, s.Date
from Songs s
inner join 
  ( SELECT artist, title, min(pos) as minPos 
    FROM songs 
    WHERE Date > $yearstart AND Date < $yearend 
    Group BY Artist, Title) m 
  on s.Artist = m.Artist and
     s.Title = m.Title and
     s.Pos = m.minPos
WHERE s.Date > $yearstart AND Date < $yearend
ORDER BY s.Artist

Finding the lowest value from list

1 Answers1