Essentially I want to order by "title", while grouping movies with the same, non-NULL "series_id" together within that order. Titles where series_id IS NULL should order by title only, while non NULL series_ids should order by "series_order" and be listed in the results based on "series"."title" within the original columns sort. I would prefer to achieve this within the query rather then loading the entire database and sorting from there.

What I have tried (MySQL 5.7.17):

ORDER BY title, series_id

    SELECT * FROM media
        ORDER BY title, series_id, series_order
    LIMIT 0, 30

This does not account for titles in a series that are not alphabetically the same (see sample data below).

ORDER BY CASE using CONCAT to sort by 'series'.'title' 'media'.'series_order'

    SELECT * FROM media
        ORDER BY CASE
            WHEN series_id IS NULL THEN title
            ELSE CONCAT((SELECT title FROM series WHERE id = media.series_id), series_order)
        END
    LIMIT 0, 30

Th results are mostly ordered by title and are grouped by 'series_id', but not all results are in order.

LEFT JOIN to include the 'series' table for sorting using the same idea

    SELECT media.*, series.title, series.id FROM media
        LEFT JOIN series ON media.series_id = series.id
    LIMIT 0, 30

The problem I am running into here is, with a LIMIT it selects the first 30 from media, and then only shows the 4 that have a series_id. And regardless of using LIMIT it groups all media with the same series_id together as one result.

Sample Data:

Primary Table: media Relevant Columns: title, series_id, series_order

Series Table: series Relevant Columns: id, title

Fiddle: http://sqlfiddle.com/#!9/efca7c/3

Looking at the fiddle, option 2 appears to be working. In the dev version it only partially orders things. So I removed the LIMIT from my dev version and it is still out of order

Dev Server SS w/ LIMIT: https://prnt.sc/1xcyq4u Dev Server SS w/o LIMIT: https://prnt.sc/1xd0a2x

With LIMIT: 3rd Rock appears grouped, but out of order, and 2012 Doomsday always appears at the end regardless of the LIMIT being 0, 27 or 12, 20

W/O LIMIT: The results are showing up sorted by the primary_id.

CodePudding user response：

Use a CASE expression to check whether seris_order value is null, if it is null then take only titlw, otherwise concatenate title with the series_order.

Query

Select case when series_order is null then title else concat(title, ' : Season ', series_order) end as title
From table_name
Order by 1;

CodePudding user response：

Based on the required convolution to achieve your desired sorting, if this was my application, I'd probably create a new column which contains the "base title" for the series and populate that value during insertion, then you could sort on that without any voodoo or eye-strain.

In the absence of modifying your table structure, I managed to downgrade a solution that was using ROW() and PARTITION into a couple of nested subqueries -- it's not what I consider beautiful.

SQL (Demo)

SELECT m2.title grouping_title, m1.title, COALESCE(m2.title, m1.title), m1.series_order
FROM media m1
LEFT JOIN (
    SELECT series_id, title
    FROM media m3
    WHERE series_order = (SELECT MIN(series_order) FROM media WHERE series_id = m3.series_id)
) m2 ON m1.series_id = m2.series_id
ORDER BY COALESCE(m2.title, m1.title), m1.series_order

You can modify the outer SELECT as you wish, but I just wanted to show what the COALESCE() function was generating. Effectively, I'm joining media table onto itself so that I can obtain the lowest series_order value for a given series_id. The title in the THAT row represents the "base title" to be used in the first rule of the sorting algorithm -- unless it is NULL, in which case, we just use the title from the parent query.

For your application output, you will want to use the m1.title and the m1.series_order.