CODEWITHSUNDEEP
mysqlsql-order-by
Lucas Hoffmann
Lucas Hoffmann

Reputation: 187

Is there any way to get this result order in MySQL?

I'm helping a friend in his website but I'm actually stuck at this point.

His database was created by himself, who has no knowledge of database modelling, so I'm having a hard time getting the results I want from it.

I've got the following query which has everything that I need:

SELECT * FROM tabelaseriesbs 
    LEFT JOIN tabelatextosbs ON tabelatextosbs.nome = tabelaseriesbs.nome and tabelaseriesbs.alter_ego = tabelatextosbs.alter_ego and tabelatextosbs.versao = tabelaseriesbs.versao
    LEFT JOIN tabelaformatosbs ON tabelaformatosbs.colecao = tabelaseriesbs.colecao,
        (SELECT distinct tabelaseriesbs.nome, tabelaseriesbs.alter_ego, tabelaseriesbs.versao FROM tabelaseriesbs
        LEFT JOIN tabelaformatosbs ON tabelaformatosbs.colecao = tabelaseriesbs.colecao,
            (SELECT tabelaseriesbs.nome, tabelaseriesbs.alter_ego, tabelaseriesbs.versao FROM tabelaseriesbs 
                WHERE 1=1 AND (tabelaseriesbs.colecao = 'Universo Marvel') AND (tabelaseriesbs.raridade = 'Comum')
            ) col 
        WHERE 1=1 AND (col.nome = tabelaseriesbs.nome) AND (col.alter_ego = tabelaseriesbs.alter_ego) 
                  AND (col.versao = tabelaseriesbs.versao) AND (tabelaformatosbs.formato >= 2) 
         ) form 
    WHERE 1=1 AND (tabelaseriesbs.alter_ego = form.alter_ego) 
              AND (tabelaseriesbs.nome = form.nome) 
              AND (tabelaseriesbs.versao = form.versao) 
              AND (tabelatextosbs.sitiada_afiliacoes = '0') 
    ORDER BY tabelatextosbs.nome ASC, tabelatextosbs.alter_ego ASC, tabelatextosbs.versao ASC, cast(tabelaseriesbs.indice AS SIGNED) ASC;

which produces the following result

indice  nome       series
2       Hawkeye    Universo Marvel
4       Hawkeye    Poderes Ocultos
6       Hawkeye    Iniciativa Vingadores
8       Hawkeye    Battle Box
10      Hawkeye    Guerra Civil
1       Mercenary  Universo Marvel
3       Mercenary  Ascensão e Queda
5       Mercenary  Ascensão e Queda - Deck

But since the id of the first Mercenary is lower than the id of the first Hawkeye, those lines should have to appear before Hawkeye, like so:

indice  nome       series
1       Mercenary  Universo Marvel
3       Mercenary  Ascensão e Queda
5       Mercenary  Ascensão e Queda - Deck
2       Hawkeye    Universo Marvel
4       Hawkeye    Poderes Ocultos
6       Hawkeye    Iniciativa Vingadores
8       Hawkeye    Battle Box
10      Hawkeye    Guerra Civil

Is there a way to do that? Or do I have to do this in the code after getting the results? Also, there are a lot more fields than those 3, if you need the complete list, just let me know.

Upvotes: 1

Views: 85

Answers (3)

Paul Spiegel
Paul Spiegel

Reputation: 31772

Begin you query with:

SELECT * 
FROM tabelaseriesbs
INNER JOIN (
    SELECT nome, MIN(cast(indice AS SIGNED)) as min_indice
    FROM tabelaseriesbs
    GROUP BY nome
) order_table USING (nome)
LEFT JOIN [...]

The subquery will find the least indice for every nome.

Then you can use min_indice in the ORDER BY clause

ORDER BY order_table.min_indice, [...]

Upvotes: 0

yanman1234
yanman1234

Reputation: 1019

You just need to add tabelaseriesbs.id ASC as an order by condition. This may disrupt your other order by conditions though depending on where you put it, but based on how I'm interpreting your query, putting this as your final condition should keep your other relative orders and order your ids as well.

Upvotes: 1

Kemal Güler
Kemal Güler

Reputation: 614

Try this

ORDER BY table.char DESC, table.id ASC

Upvotes: 0

Related Questions