Sort select displaying something at the end after sorting

Question

I have a table called Tabela1 and another call Tabela2:

Tabela 1             Tabela2
Indice, pessoa_id    sts, pessoa_id

The index goes from 0 to 10, and sts can be ON-LINE, OFF-LINE OR ON-LINE-PAUSADO.

I wish that when I gave the select, come back ordered, but if any of these people are with the sts equal to 'online-paused', come at the end of the select.

Example:

indice, pessoa_id, sts
4     , 2        , 'ON-LINE'
5     , 2        , 'ON-LINE'
6     , 2        , 'ON-LINE'
8     , 2        , 'ON-LINE'
1     , 2        , 'ON-LINE-PAUSADO'
2     , 2        , 'ON-LINE-PAUSADO'

Answer 1

You need to sort by status and then by descending index column.

Your query would look like this:

SELECT
    t1.indice,
    t1.pessoa_id,
    t2.sts
FROM
    tabela1 t1
INNER JOIN tabela2 t2 ON t1.pessoa_id = t2.pessoa_id
ORDER BY
    t2.sts, t1.Indice DESC

This form will work because the name of your status is in alphabetical order, however, if you have more status, you can create a new column and sort it by adding an integer in the order of the status you want to return (1, 2, 3...).

Example:

ALTER TABLE tabela2
ADD COLUMN ordem_status INT

And in the same query above, you will sort through this new column, getting:

ORDER BY t2.ordem_status, t1.Indice DESC

Answer 2

Something should not be right in your definition of tables, because in Tabela 2 must be the column Indice, otherwise linking with each other the result would not be correct.

---

Assuming that the Tabela 2 there is the column Indice, the following query returns you what you want:

SELECT      T1.pessoa_id
        ,   T2.indice
        ,   T2.sts
FROM        tbl1 T1
INNER JOIN  tbl2 T2 ON T2.pessoa_id = T1.pessoa_id
ORDER BY    (   CASE T2.sts 
                WHEN 'ON-LINE'  THEN 1 
                WHEN 'OFF-LINE' THEN 2 
                ELSE 3 
                END
            )
        ,   T2.indice

If in the future you have another kind of status, just manage the ordering by adding or changing the states (or the numbering for each one).

Answer 3

Simply add the syntax to your query:

. . .  order by Tabela2.sts, Tabela1.Indice