Assign id in reverse order?

Question

SQL

For example, there is such a base
id color
1 green
2 red
3 blue
4 white
which query can be done like this:
4 green
3 red
2 blue
1 white

Answer 1

None.
If playful hands reach out to touch the id, then this can only mean two things
- either you need to hit the handles with a ruler and explain to the owner that the unique identifier is not a beauty chick, but an identifier tightly tied to a row in the database that never changes, and dies with her.
- either a unique identifier for this table is not needed at all, and it must be removed

Answer 2

An interesting task, for interviews the most something. Easiest way to join by row count
Base schema

CREATE TABLE tmp (
  id INT,
  color VARCHAR(255)
);

Data

INSERT INTO tmp 
  (id, color)
VALUES 
  (1, 'зеленый'),
  (2, 'красный'),
  (3, 'синий'),
  (4, 'белый')
;

Request

SELECT id, color FROM (
  SELECT 
    id,
    @join_pk_1:[email protected]_pk_1 + 1 as join_pk
  FROM tmp, (select @join_pk_1:=0) as join_pk_1
  ORDER BY id DESC
) as tbl1
LEFT JOIN (
  SELECT 
    color,
    @join_pk_2:[email protected]_pk_2 + 1 as join_pk
  FROM tmp, (select @join_pk_2:=0) as join_pk_2
) as tbl2
ON tbl1.join_pk = tbl2.join_pk

Result

id 	color
4 	зеленый
3 	красный
2 	синий
1 	белый

Answer 3

It's simple, you can do it like this, use only newID naming it as ID if necessary, deleting the second column

select ROW_NUMBER() over (order by id desc) newID,id,color from

(
select      1 id, 'зеленый' color
union all select 2, 'красный'
union all   select 3, 'синий'
union all  select 4, 'белый'
) yourtable
order by id