How to use order by clause for a column not in select list

[rp1504]

Hi,

I have to order the query by a column that is not in select clause, but exists in the table structure.

Pls. let me know any solutions.

 

[SQLSister]

Just add an order by clause to the statment withthe field you want, it doesn't have to be in th eselect list (although I would personally put it inthe select list while testing to make sure I was getting the results I wanted.)

Questions about posting. See faq183-874

 

[rp1504]

I am sorry,

I did not mention that I am using "SELECT DISTINCT" statement.
This does not permit you to use a column in the order by clause that is not in the Select clause.

 

[SQLSister]

Well as I see it you have have two choices. Put the field inteh select list or write a query that enables you to not use Select Distinct. If you provide your query we could possibly provide some advice on the latter, but the first choice is definitely the simplest.


Questions about posting. See faq183-874

 

[SQLBill]

You could try a subselect....

SELECT column1, column2
FROM (SELECT DISTINCT column1, column2, column3
      FROM tablename)
ORDER BY column3

-SQLBill

 

[rp1504]

Thanks SQLBill ; this worked !
I had to however provide an alias name to the sub query. Thanks again also Thanks SQLSISTER.

 

[SQLBill]

True, I forgot about aliasing when a select is used for the FROM. Oh well, I'm glad you caught that.

-SQLBill