SQL Multiple sorting and grouping

Question

EDIT: I'm using DQL

I'm looking for help with an SQL query.

My table has a list of movies, each with a title, seriesName and seriesNumber. Is it possible to order them so the Titles are listed A-Z, but when a series occurs, that series is grouped together, with the seriesName being placed alphabetically as if it were inthe movieTitle column, and the entries in a series ordered by seriesNumber.

Bad explanation, but basically what I want is this:

MovieTitle                  SeriesName          SeriesNumber
Blade Runner                NULL                NULL
District 9                  NULL                NULL
Hot Fuzz                    NULL                NULL
I am Legend                 NULL                NULL
Fellowship of the Ring      Lord of the Rings   1
Two Towers, The             Lord of the Rings   2
Return of the King          Lord of the Rings   3
Lost in Translation         NULL                NULL
Matrix, The                 Matrix              1
Matrix Reloaded, The        Matrix              2
Matrix Revolutions, The     Matrix              3
Requiem for a Dream         NULL                NULL
This is Spinal Tap          NULL                NULL
Zodiac                      NULL                NULL

Thanks in advance.

DQL, doing some stuff in Symfony2 – David Undy May 31 '12 at 01:28 — David Undy, May 31 '12 at 01:28

score 2 · Accepted Answer · answered May 31 '12 at 01:21

2

SELECT * FROM x
ORDER BY CASE 
   WHEN SeriesName is NOT NULL THEN SeriesName ELSE MovieTitle  END
   , SeriesNumber

You may have to do it this way

SELECT * FROM(
  SELECT *, CASE WHEN SeriesName is NOT NULL THEN SeriesName ELSE MovieTitle END SortOrder  FROM x
)
ORDER BY SortOrder,SeriesNumber

answered May 31 '12 at 01:21

dfb

13,133
2
31
52

Thanks a lot, I tried getting it to work in straight DQL, but it was a bit of a hassle, ended up using raw sql. Appreciate the help :) – David Undy May 31 '12 at 02:53

SQL Multiple sorting and grouping

1 Answers1

Linked