sort query result without selecting that column but sort by that column?

user_1468481 0
Viewed 37959

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

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

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

db2 sorting sql database
5 Answers

Another syntax that may be easier, depending on how you think about it is using the with keyword. This explicitly creates a named temporary table with the desired ordering, then queries from that. The order of the new query will be the same as the temporary tables ordering.

WITH temp_table AS (SELECT   *
            FROM emp
        ORDER BY empno)

SELECT empname, salary, status
  FROM temp_table
 WHERE salary > 5000;

The answer by @jaychapani 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 the two, which can be helpful especially if you have a long subquery that does other things.

user_13758220

I used below query to solve this problem. In this case we can sort query result without displaying the column:

WITH temp_table
     AS (select distinct(s1.Name),s1.id
         from students s1
         where marks>75
         order by right(s1.Name ,3) asc,s1.id asc
        )

SELECT Name
  FROM temp_table;
user_96375050

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

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