SQL reordering and selecting columns-CodePudding

I started learning SQL recently and the problem I often encounter is reordering the table by certain criteria. For example, I have a table "films" and it has several variables including "country" and "title". I tried to reorder the table by applying some operations:

SELECT
  t.country, t.title, t2.titlecount AS tmp1, t2.math AS tmp2
FROM films AS t
INNER JOIN (SELECT country, COUNT(title) AS titlecount, SUM((budget - duration) / release_year) AS math
FROM films
GROUP BY country
ORDER BY titlecount DESC) AS t2 on t.country = t2.country
ORDER BY tmp1 DESC, tmp2, (t.budget - t.duration) / t.release_year, title

Then, I end up with 4 columns "country", "title", "tmp1", and "tmp2". Can I drop the two tmp columns and only retrieve "country" and "title"? Since the two tmp variables were needed for reordering, I cannot drop them from the start. Are there some ways to deal with these situations? Thank you.

CodePudding user response：

Nested query should help:

select s.country, s.title from (SELECT
      t.country as country, t.title as title, t2.titlecount AS tmp1, t2.math AS tmp2
    FROM films AS t
    INNER JOIN (SELECT country, COUNT(title) AS titlecount, SUM((budget - duration) / release_year) AS math
    FROM films
    GROUP BY country
    ORDER BY titlecount DESC) AS t2 on t.country = t2.country
    ORDER BY tmp1 DESC, tmp2, (t.budget - t.duration) / t.release_year, title) as s;