DISTINCT results in ORA-01791: not a SELECTed expression

Question

select DISTINCT a.FNAME||' '||a.LNAME    from AUTHOR a, books B, BOOKAUTHOR ba, customers C, orders    where C.firstname='BECCA'       and C.lastname='NELSON'       and a.AUTHORID=ba.AUTHORID       and b.ISBN=bA.ISBN    order by a.LNAME 

gives ORA-01791: not a SELECTed expression but works without DISTINCT.

How to make it work?

Answer 1

Just add LNAME as a column on its own in the select clause:

SELECT full_name FROM (  select DISTINCT a.FNAME||' '||a.LNAME AS full_name, a.LNAME  from AUTHOR a, books B, BOOKAUTHOR ba, customers C, orders  where C.firstname='BECCA'    and C.lastname='NELSON'    and a.AUTHORID=ba.AUTHORID    and b.ISBN=bA.ISBN  ) order by a.LNAME 

If you only want the first column in the output, you can put the whole thing in a subquery.