How can I hide a column using a Select statement? 2

[sgursahaney]

I would like to list a column from my database in a particular sequence. To accomplish this, I have created a column called SORT_ORDER that allows me to sort the records when I select them. I have the created the following SELECT statement:

SELECT PRODUCT_NAME, SORT_ORDER FROM PRODUCTS
ORDER BY SORT_ORDER ASC

But, I don't want the Sort Order to actually show up in my output. When I take it out, SQL Server complains that I cannot use a column in the ORDER BY clause if it is not specified in the SELECT list. How can I hide the SORT_ORDER column from my output?

Thanks in advance.

 

[tlbroadbent]

SQL Server is consistent with the ANSI 92 SQL Standard. You cannot order by a column that is not in the SELECT list. "Order by" clause applies to a result set. If the result set of your query doesn't have a column which you want to use in "order by" how is the "order by" clause to be evaluated?

Several RDMS deviate from the ANSI 92 standard, notably MS Access but SQL Server adheres to the standard.

You can hide the column by using a sub-query.

SELECT PRODUCT_NAME FROM
(SELECT TOP 100 PERCENT PRODUCT_NAME, SORT_ORDER
FROM PRODUCTS
ORDER BY SORT_ORDER ASC) As S

Note the use of TOP 100 PERCENT in the sub-query. This is another SQL requirement. The TOP clause must be included to use ORDER BY in a sub-query. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[sgursahaney]

Your suggestion worked great -- even with a more complex query I have using UNIONs. I believe I understand everything you have done except for the AS clause at the end. What does that do? When I modified my query based on your suggestion, I used an AS clause to give my column new names, but I don't understand what the AS clause at the end does. Thanks.

 

[tlbroadbent]

The AS cluase at the end has the same function as your use of it. It provides and alias name for the sub-query. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin