Re: DISTINCT/ORDER BY

A copy of this was sent to hummels_at_tce.com (if that email address didn't require changing) On Fri, 22 Oct 1999 21:08:15 GMT, you wrote:

>I have a query that needs return a list of CATEGORY and sorted by
>another field. There may be duplicate CATEGORY values, so I need to do
>something similar to DISTINCT.
>
>The following sorts correctly, but shows duplicates.
>
>select category
>from TheTable
>ORDER BY thesequence
>
>
>So I added DISTINCT to it:
>
>select distinct category
>from TheTable
>ORDER BY thesequence
>
>and get the error ORA-01791: not a SELECTed expression
>
>If I add thesequence to the SELECT, then I loose the DISTINCT. How can
>I do this?
>

You are asking to sort category by thesequence BUT only want distinct categories. If I have in the table:

category     thesequence
--------     ------------
A            1
A            999999999999

*which* "thesequence" would you like to use?

Perhaps something like:

select category
from theTable
group by category
order by min(theSequence)
/

will do it. it will pick the minimum sequence assigned to a category and use that to sort.

>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Oct 23 1999 - 08:13:24 CDT