0

The title is crap because I'm not actually sure what this is called. I ask because I can't be the first to want to do this. (I use "whatever" or WE to mean doesn't matter)

Series table:
id seriesName
1  WE
2  WE

id title series seriesPosition seriesName
1  A     0      0              ""
 2  B     1      0              "Part I"
 3  F     1      1              "Part II"
 4  D     1      2              "Part III"

 5  C     2      0              ""
 6  E     2      1              "The return"
7  G     0      0              ""

I'd like to sort is so the data set is in the order the table above is in. That is to say that the title is sorted into ascending order but the rest of a series follows the first in that series. This is why 3 and 4 follow 2 despite 5 coming before 3 alphabetically.

I'm guessing this can't be done in SQL though. So instead I'd like to select things (uniquely) with a non-zero series, but I'd like to select the lowest-titled one. Then when I'm processing results I can see the non-zero seriesId and load the data as needed.

I'm using SQLite.

I don't think I can use group by because all the records with seriesIds equal to zero are logically unique. Can this be done in SQL (or what's the closest I can get), or will it have to be done my side of the interface?

I also say families because that's sort of what a series represents, a family of books.

Alec Teal
  • 5,770
  • 3
  • 23
  • 50
  • 1
    Oracle has CONNECT BY with ORDER SIBLINGS clause. but wait: is it simply that you want to order by SERIES, then SERIES_POSITION? – Randy Feb 12 '14 at 21:12
  • @Randy is it patented? – Alec Teal Feb 12 '14 at 21:19
  • Don't think so - here is an IBM doc for the same syntax: http://publib.boulder.ibm.com/infocenter/soliddb/v6r3/index.jsp?topic=/com.ibm.swg.im.soliddb.sql.doc/doc/select.level.and.order.siblings.by.example.html – Randy Feb 12 '14 at 21:22
  • 1
    Unless I'm missing something, it sounds like you just want to [ORDER BY multiple columns](http://stackoverflow.com/questions/2051162/sql-multiple-column-ordering)? – rutter Feb 12 '14 at 21:27
  • @rutter if you order by multiple columns, they are sorted first, then records with an equal position by the first sort are sorted by the second, I want things with the same series together, so I'd have to order by that first, then alphabetically, which would just be the series in order of ID, sorted alphabetically, which is silly. – Alec Teal Feb 12 '14 at 22:11
  • What are "A" and "G"? How do they know to go in the positions they are in? – Gordon Linoff Feb 12 '14 at 22:48
  • @GordonLinoff alphabetical order..... – Alec Teal Feb 12 '14 at 23:16
  • I might be getting it: you want a list of all non-series or first-in-series books in alphabetical order, and into that we must splice all nth-in-series books in series order? – rutter Feb 13 '14 at 00:03
  • @rutter exactly. What I have done instead is ordered all the books with a 0 position (no series => 0th position and has a series => book with 0th position) and ordered that alphabetically, then when flicking through the results if the series is non-zero, loading them in. – Alec Teal Feb 13 '14 at 00:37

2 Answers2

1

Suffice to say from the above comments: the sort order here is confusing!

I conceptualize it as a two-step process:

  • Get a list of all non-series or first-in-series books, sorted alphabetically by title
  • Splice into that list all nth-in-series books, in series order

Step one is easy:

SELECT title, series, seriesPosition, seriesName
FROM books
WHERE seriesPosition = 0
ORDER BY title;

Step two gets tricky. I decided to invent an extra column by concatenating the title of the series position, and named this column sortKey:

SELECT title, series, (title || seriesPosition) sortKey
FROM books
ORDER BY sortKey;

This custom sortKey is essential, but we can't just use each book's title; we need to use the title of the first book in its series. We can find this using a self-join:

SELECT a.title, a.series, a.seriesPosition, a.seriesName, (b.title || a.seriesPosition) sortKey
FROM books a
INNER JOIN books b ON a.series = b.series
WHERE a.series = 0 OR b.seriesPosition = 0
ORDER BY sortKey;

Unfortunately, that includes duplicate entries for all books in series 0.

There might be an elegant way to get rid of those, but since it's 5pm I settled for a union between two queries (one each for in-series and non-series books):

SELECT title, series, seriesPosition, seriesName, title sortKey
FROM books
WHERE series = 0
UNION
SELECT a.title, a.series, a.seriesPosition, a.seriesName, (b.title || a.seriesPosition) sortKey
FROM books a
INNER JOIN books b ON a.series = b.series
WHERE a.series != 0 AND b.seriesPosition = 0
ORDER BY sortKey;

You can see a live demo on SQLFiddle.

rutter
  • 11,242
  • 1
  • 30
  • 46
0

Rutter's answer is great but really it is much simpler. This works with MySQL:

SELECT *,true as HasSeries,
    (select SeriesName from Series where Series.SeriesId = Books.SeriesId) as orderName FROM Books WHERE SeriesId != 0
UNION
SELECT *,false as HasSeries,Title as orderName FROM Books WHERE SeriesID = 0
ORDER BY orderName asc, seriesPosition asc;

I would imagine it works with SQLite if it has unions, it is not ideal as it may put more strain on the SQL server than I'd like but yes, this is one way of doing it!

Alec Teal
  • 5,770
  • 3
  • 23
  • 50