CODEWITHSUNDEEP
mysqlsqlsql-order-by
shredding
shredding

Reputation: 5591

Multiple order by with SQL

I have a table that organizes the shows of a program (every program will be performed in multiple shows). I need to have a view where they are ordered into programs but chronologically.

The SQL Query is much longer in real but in short this:

SELECT * FROM show_table ORDER BY program, timebegin

results in this table:

    # show_table

program  |   timebegin     
3399        Do, 01. Nov. 2012 20 Uhr
3399        Fr, 02. Nov. 2012 20 Uhr
3399        Sa, 03. Nov. 2012 20 Uhr
3401        Do, 08. Nov. 2012 20 Uhr
3401        Fr, 09. Nov. 2012 20 Uhr
3401        Sa, 10. Nov. 2012 20 Uhr
3719        So, 04. Nov. 2012 20 Uhr
3739        Mo, 12. Nov. 2012 20 Uhr
3777        So, 04. Nov. 2012 12 Uhr

Hence, the programs are itself ordered by time, but I want to have the order slightly different, like that:

program  |   timebegin     
3399        Do, 01. Nov. 2012 20 Uhr
3399        Fr, 02. Nov. 2012 20 Uhr
3399        Sa, 03. Nov. 2012 20 Uhr
3777        So, 04. Nov. 2012 12 Uhr
3719        So, 04. Nov. 2012 20 Uhr
3401        Do, 08. Nov. 2012 20 Uhr
3401        Fr, 09. Nov. 2012 20 Uhr
3401        Sa, 10. Nov. 2012 20 Uhr       
3739        Mo, 12. Nov. 2012 20 Uhr

Now everything is ordered by timebegin, while programs are not intercepted. Basically, I do not want to sort by program, but by timebegin, but group all programs together.

Is this possible with pure mySQL or do I have to apply server side logic?

Upvotes: 0

Views: 117

Answers (4)

Aleksi Torhamo
Aleksi Torhamo

Reputation: 6632

You can do it like this:

SELECT show_table.program, show_table.timebegin
FROM show_table
INNER JOIN (
    SELECT program, MIN(timebegin) AS firsttime
    FROM show_table
    GROUP BY program
) AS groups
ON (show_table.program = groups.program)
ORDER BY groups.firsttime, show_table.program, show_table.timebegin;

Basically, there's another query that gets the minimum timebegin (called firsttime) for each program group, then the query is joined to the original table. Then we just order by firsttime to get the program groups ordered correctly and by timebegin to get the rows inside a program group ordered correctly. The program field in ORDER BY between them is in there in case two program groups happen to have the same firsttime, so we don't mix them in that case.

Upvotes: 2

Marco
Marco

Reputation: 57573

Watching your desired result, I think you could use

SELECT * FROM show_table 
ORDER BY timebegin, program

If you want all programs for every timebegin, try this

SELECT timebegin, GROUP_CONCAT(program)
FROM show_table 
GROUP BY timebegin
ORDER BY timebegin

Take a look at GROUP_CONCAT man page.

Upvotes: 0

Tim Lehner
Tim Lehner

Reputation: 15251

Basically, I do not want to sort by program, but by timebegin, but group all programs together.

These requirements are at odds with each other. You'll have to choose which column is more important. While ordering by timebegin first, the only way that programs will stay grouped together is if the data happens to be in that order, as in, you don't have any instance of program 2 happening in between two instances of another program. This is somewhat doubtful.

Upvotes: 0

John Woo
John Woo

Reputation: 263693

Did you try ORDER BY timeBegin, program? It's much easier when timeBegin's data type is DateTime. Right?

Upvotes: 0

Related Questions