Error When Using Distinct - ORA-01791: not a SELECTed expression

The Problem
I had a query that was working, but when I added a DISTINCT to it started throwing an error.  The error I was receiving was ORA-01791: not a SELECTed expression.  It didn't give me which field it didn't like, so I was confused.


The Solution
What I found was that once DISTINCT was in play all the fields in my ORDER BY had to be in the SELECT.  All of the ORDER BY fields had to be fields and not calculations.

How to insert an ampersand into an Oracle field

I was trying to insert a value into a varchar2 field.  The value contained an ampersand.  Something like "A&B Home Remodeling".  It kept giving me an error like &B was a variable.


What worked for me was storing it like this:
'A'||'&'||'B'


It like that much better.  I also saw suggestions to chr(38).  I did not try that, so I am not sure if it works, but I am guessing it would be very similar to what I did.  Something like:
'A'||chr(38)||'B'

How to do Select Top in Oracle

In SQL Server I often do a SELECT TOP 10 FROM [TableName].

To get the bottom ten I found that I could ORDER BY DESC.

In Oracle things are a bit different. In Oracle they have a ROWNUM system field. To get the first ten records you just have to get the ROWNUMs that are 10 and below like the following:

SELECT * FROM [TableName] WHERE ROWNUM >= 11