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

Question

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?

Thanks for your time.. It is working..I selected from multiple tables but the empno is ambiguous.. Now updated.. working.. — Mr.Chowdary, Jul 19 '12 at 13:58

score 12 · Accepted Answer · answered Jul 19 '12 at 05:29

12

Your syntax seems correct to me except dot(.) at the end. After removing dot if doesn't work...

Try something like

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

answered Jul 19 '12 at 05:29

jaychapani

1,501
1
13
31

HI jaychapani, Your query is not working.. Can you update it correctly ? – Mr.Chowdary Jul 19 '12 at 05:38
4

As per IBM's documentation "The column specified in the ORDER BY clause does not need to be included in the SELECT clause." – jaychapani Jul 19 '12 at 05:46
Can you just post screen print of result of query "select * from emp" – jaychapani Jul 19 '12 at 05:47
Thanks for your time.. It is working..I selected from multiple tables but the empno is ambiguous.. Now updated.. working.. – Mr.Chowdary Jul 19 '12 at 05:49
1

@Mr.Chowdary & jaychapani ORDER BY in a subselect is not guaranteed to do anything. Tables have no order, result sets do. – philipxy Jul 04 '17 at 10:50
1

This will sometimes work... but you can't depend on it as the order by is not guaranteed to be correct. – Sebastiaan van den Broek Feb 07 '22 at 05:12

score 2 · Answer 2 · answered Jan 19 '19 at 19:22

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.

score 1 · Answer 3 · edited Sep 29 '20 at 13:31

1

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;

edited Sep 29 '20 at 13:31

fcdt

2,371
5
14
26

answered Sep 29 '20 at 13:00

archit agarwal

91
1
2

score 0 · Answer 4 · answered Nov 22 '13 at 10:43

0

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
)

answered Nov 22 '13 at 10:43

Krzysiek

615
8
19

BizApps · Answer 5 · 2012-07-19T05:39:45.790

-3

try this

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

make sure that columns and table name really exist.

have a look at: ORDER BY clause

Best Regards

edited Jul 19 '12 at 05:39

answered Jul 19 '12 at 05:29

BizApps

6,048
9
40
62

the dot was removed and i added asc (asceding) or use desc for descending order of your data. – BizApps Jul 19 '12 at 05:37
I think not all SQL servers supports order by without specifying column in select – kpostekk Dec 30 '21 at 16:51

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

5 Answers5