I'm having to create a query that returns the data sorted by a specific field, for example name, but the first return line must be the last id. Can you do this with sql?
Let's suppose I have the user table:
id nome
1 João
2 Marcelo
3 André
4 Vanessa
The return should be:
id nome
4 Vanessa
3 André
1 João
2 Marcelo
If anyone has any ideas for this.
Follow a SQLFiddle
Possible solution:
SELECT
id,nome
FROM
usuario
JOIN
(SELECT MAX(id) AS ultimo FROM usuario) AS dummy
ORDER BY id != ultimo, nome
Explanation:
The subquery (SELECT MAX(id) AS ultimo FROM minha.Base.minhaTabela) AS dummy causes id greater to be returned for all rows, under the name of column ultimo .
By taking the special case of ORDER BY , sorting is done by name.
See working on SQL Fiddle
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
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 greater ID , and we make it available for consultation in the main query.
Then we use a comparison directly in the ORDER BY clause.
Result:
| ID | NOME |
|----|---------|
| 4 | Vanessa |
| 3 | André |
| 1 | João |
| 2 | Marcelo |
Just use something like this order by id desc
Just complementing for those who could not try to understand
SELECT *
FROM teste
ORDER BY id DESC, nome ASC
So:
The first select takes the information from the last record, the second does the total sort by the field name, when joining the tables it already does what the question expects the union of the two SQL, eliminating even the code that repeats!
Because it does not repeat with a union it does a distinct so there is no repetition, according to link microsoft , 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