The result of a sort request without selecting this column, but sort by this column?

Question

I have a query, I need to sort the result from the DB2 database. The query will select the columns empname,salary,status . But I have to sort the result by empno order
But the request does not work. This is a request.

 select empname, salary, status from emp where salary>5000 order by empno 

Can you update the query to sort empno without using it when selecting columns?

Answer 1

Your syntax seems right to me, except for the period (.) At the end. After deleting the point, if it does not work ...

Try something like

 SELECT empname, salary, status FROM (SELECT * FROM emp ORDER BY empno) WHERE salary > 5000 

Answer 2

I'm not sure, but the fastest way in DB is something like this:

 SELECT empname, salary, status FROM ( select empname, salary, status, empno from emp where salary > 5000 order by empno ASC ) 

Answer 3

Another syntax that might be simpler, depending on how you relate to it, uses the with keyword. This explicitly creates a named temporary table with the desired order, and then queries it. The order of the new query will be the same as the order of the temporary tables.

 WITH temp_table AS (SELECT * FROM emp ORDER BY empno) SELECT empname, salary, status FROM temp_table WHERE salary > 5000; 

@Jaychapani's answer is more concise and functionally does the same thing, but the with syntax is powerful for quite a few other use cases and visually separates them, which can be useful, especially if you have a long subquery that does other things.

Answer 4

try it

 select empname, salary, status from emp where salary>5000 order by empno asc 

make sure the columns and table name really exist.

Take a look: ORDER BY clause

Best wishes