Sorting in query - Leave last registration in first and then sort by a field

Question

I’m having to create a query that returns the data ordered by a specific field, for example name, but the first return line should be the last id. You can do this with sql?

Let’s assume I have the user table:

id    nome
1     João
2     Marcelo
3     André
4     Vanessa

The return must be:

id    nome
4     Vanessa
3     André
1     João
2     Marcelo

If anyone has any ideas for that.

Follows a Sqlfiddle

Answer 1

Possible solution:

SELECT
   id,nome
FROM
   usuario
JOIN
   (SELECT MAX(id) AS ultimo FROM usuario) AS dummy
ORDER BY id != ultimo, nome

Explanation:

1. The subquery (SELECT MAX(id) AS ultimo FROM minha.Base.minhaTabela) AS dummy causes the id major is returned to all rows under the column name ultimo.

2. The ORDER BY first orders by id other than id maior, that is to say, false to the greatest id, what makes this the first line of the query.

3. excluding the special case of id, the ordination is made by the name.

See working on SQL Fiddle

Answer 2

An auxiliary table can be used for this scenario:

CREATE TABLE #temp (id int, nome nvarchar(max))
INSERT INTO #temp (id, nome)
    SELECT id, nome from tabela where id = (select max(id) from tabela)
INSERT INTO #temp
    SELECT id, nome from tabela where id <> (select max(id) from tabela) order by nome
SELECT id, nome FROM #temp
DROP TABLE #temp

Answer 3

SELECT usr.* 
FROM   usuario AS usr, 
       (SELECT Max(id) AS maxid 
        FROM   usuario) AS src 
ORDER  BY src.maxid != usr.id, 
          usr.nome 

Explanation:

First we define in a subquery which is the largest ID, and make it available for consultation in the main query.

Then we use a comparison directly in the clause ORDER BY.

Upshot:

| ID |    NOME |
|----|---------|
|  4 | Vanessa |
|  3 |   André |
|  1 |    João |
|  2 | Marcelo |

Answer 4

just use something like this order by id desc

only complementing for those who failed to understand

SELECT *
FROM teste
ORDER BY id DESC, nome ASC

Answer 5

Thus:

The first Select takes the information of the last record, while the second makes the total ordering by the name field, when joining the tables it already does what the question expects the union of the two SQL, even eliminating the code that repeats! Why don’t you repeat with a union he makes a distinct for no repetition, second microsoft link, is very well explained.

SELECT id, nome FROM (
      SELECT id, nome FROM testdb.tabela30 ORDER BY id DESC LIMIT 1
) AS tabelaidmax
union
SELECT id, nome from (SELECT id, nome FROM testdb.tabela30 ORDER BY nome asc
) as tabelanomeasc